Jump to content
Larry Ullman's Book Forums

Recommended Posts

I have a MySQL database model where I have different types of users, each having common field names, like 'username', 'password', 'first_name', 'last_name', etc. etc. I've designed it so that these common fields are stored in a 'base' table called 'users' and specific fields that apply to each type of user are stored in derived 'sub-tables' for each type of user.

 

Here is what it looks like:

 

 

capturelip.jpg

 

 

I know that this is a better design than having the 'common' fields stored in every 'user' table.

The problem I'm having is I'm trying to figure out how I'm going insert a new user. What would be the best way to handle this?

 

Initially, I'm thinking I could do an insert like this (let's say I wanted to insert a student):

 

<?php

// Begin Transaction

// Insert the common data into the 'users' table

// Get the last inserted ID

// Insert the student-specific data into the 'students' table along with the last inserted ID

// Commit

// If problem occured

   // Rollback

?>

 

But that just seems like a very crappy way to do it. I would like to have it done in one swift statement.

 

So I haven't had much luck finding clear solutions online, but I recall one person mentioning the use of updatable views for each subtype, where the view would perform an inner join on the subtype table and the base table, and you could insert and update using the single view.

 

I have tried to create a view but keep getting the error:

 

#1356 - View '[view name]' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them.

 

Where [view name] is the name of my view. I get this error even when just selecting one column from one table and I know the names are right.

 

 

Would the 'view' approach be the best way to go, assuming I can figure out how to get it to work?

 

Is there a better way than what I've mentioned so far?

 

 

 

Much thanks,

 

Zane

 

 

 

EDIT:

 

Oh, and the PK 'user_id' is an auto-incremented INT in the users table.

 

EDIT 2:

 

Got my view to work. Turns out I had to specify the SQL SECURITY line as INVOKER instead of the default DEFINER. Going to try to see if I can perform inserts and updates on this view...

Link to comment
Share on other sites

Ok,

 

It turns out that only one underlying table in a view gets affected when performing inserts / updates. To get around this, I tried writing a trigger for the view which auto-inserts into the subtype table, but it also turns out MySQL doesn't support triggers on views.

 

So not sure... I think my only choice would be to use the first possible method I described, which does 2 separate queries. Even though it's a seemingly clunky solution, I think it's not so bad considering they're contained within a transaction.

Link to comment
Share on other sites

 Share

×
×
  • Create New...