[RESOLVED] Two tables for two types of users?

gwerner

I'm building a web app that allows two unique users to register an account. I had planned on storing user A in table A and user B in table B. This would include name, address, etc., user name and password.

Is that the right approach or should I store all like data in table A and any extra data for user B in table B? Essentially having one table that has all the users and their basic user info such as user names and passwords. Then linking the two tables together by identifying if the user is a user B, then retrieve from table B.

As I'm building this I'm running into instances where I need to retrieve user data, but don't know the type of user. Currently if a user logs in, I have to always look in two tables. It seems like I should identify the type of user by setting it as a session variable and then look in the appropriate table(s).

Hopefully that makes sense and I appreciate any feedback.

samuelcook

I would recommend storing all base information in one table for essentials like username,pass,email, etc.

This table would be your "parent" table where setting the id would be your primary key
(eg.)

CREATE TABLE IF NOT EXISTS `parent`(
`id` INT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(`id`)
);

Then using a foreign key on your two "unique" tables that coincide with your parent table that reference your parent id
(eg.)

CREATE TABLE IF NOT EXISTS `child1`(
`id` INT(10) NOT NULL AUTO_INCREMENT,
`parentID` INT(10) NOT NULL,
FOREIGN KEY(parentID) REFERENCES parent(id)
PRIMARY KEY(`id`)
);

CREATE TABLE IF NOT EXISTS `child2`(
`id` INT(10) NOT NULL AUTO_INCREMENT,
`parentID` INT(10) NOT NULL,
FOREIGN KEY(parentID) REFERENCES parent(id)
PRIMARY KEY(`id`)
);

Additionally you can set the cascade on your foreign keys for UPDATE | DELETE

Then when you need to SELECT the information you can just use the id from the parent table with a LEFT JOIN

SELECT p.*,c1.*,c2.* FROM parent p
LEFT JOIN child1 c1 ON c1.parentID=p.id
LEFT JOIN child2 c2 ON c2.parentID=p.id
WHERE ...

And because the foreign key is always going to be unique you don't have to worry about selecting other users info. If information for user is in child1 table then child2 table just comes back blank, and vice versa.

Additionally, you wouldn't even need to use a foreign key, so long as you still go by the same method of using a parentID in your children table but help when you go to update information.

Hope this helps.

Cheers.

gwerner

Thanks. That is what I'm going to do. I think that will best meet my needs. One thing to note for those that may stumble upon this later. I don't believe foreign keys are allowed with the myISAM engine. Thanks again.