Search the Community

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: 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...