This appears to be a situation where you want to model inheritance in your database.
Rather than storing the user_details_ table names in the user_types table, something akin to the following may serve you better:
CREATE TABLE IF NOT EXISTS 'mydb'.'user' (
'user_id' INT NOT NULL,
'type_id' INT NOT NULL,
'commonfield1' datatype (NOT) NULL,
'commonfield2' datatype (NOT) NULL,
'commonfield...' datatype (NOT) NULL,
PRIMARY KEY ('user_id', (other field as needed)),
UNIQUE INDEX 'adv_id_UNIQUE' ('user_id' ASC),
INDEX 'adv_type_idx' ('type_id' ASC),
CONSTRAINT 'adv_type'
FOREIGN KEY ('type_id')
REFERENCES 'mydb'.'user_type' ('type_id')
ON DELETE NO ACTION
ON UPDATE NO ACTION)
CREATE TABLE IF NOT EXISTS 'mydb'.'user_type' (
'type_id' INT NOT NULL,
'type_name' VARCHAR(45) NOT NULL,
UNIQUE INDEX 'type_id_UNIQUE' ('type_id' ASC),
UNIQUE INDEX 'type_name_UNIQUE' ('type_name' ASC),
PRIMARY KEY ('type_id'))
//TABLES WITH SEPARATE SET OF FIELDS
CREATE TABLE IF NOT EXISTS 'mydb'.'user_details_admin' (
'user_id' INT NOT NULL,
'type_id' INT NOT NULL,
'adminfield1' datatype (NOT) NULL,
'adminfield...' datatype (NOT) NULL,
PRIMARY KEY ('user_id'))
CONSTRAINT user_type_FK
FOREIGN KEY ('user_id', 'type_id')
REFERENCES 'mydb'.'user' ('user_id', 'type_id')
ON DELETE NO ACTION
ON UPDATE NO ACTION)
CREATE TABLE IF NOT EXISTS 'mydb'.'user_details_moderator' (
'user_id' INT NOT NULL,
'type_id' INT NOT NULL,
'moderatorfield1' datatype (NOT) NULL,
'moderatorfield...' datatype (NOT) NULL,
PRIMARY KEY ('user_id'))
CONSTRAINT user_type_FK
FOREIGN KEY ('user_id', 'type_id')
REFERENCES 'mydb'.'user' ('user_id', 'type_id')
ON DELETE NO ACTION
ON UPDATE NO ACTION)
This design assumes that a user may be of one and only one type. You'll need to insure that, for example, a moderator
is only added to the user_details_moderator
table using triggers or views, and/or by handling it in your application code. MySQL doesn't implement check constraints on tables. You'll likely want to create views, anyway, in order to avoid having to write the JOIN
between the user
table and the sub-type tables every time you want to query a specific sub-type.
Note: The INDEX
on type_id
in the user
table may not be useful or necessary.
This is not the only way to model your data. If you have few fields that are distinct between types and/or are willing to have fields you know will be NULL
in your table, you can just add all the fields to the user
table. Other than the a priori NULL
fields issue, a major difference between these approaches comes with the addition of a new user_type
with new distinct fields. In the example I provided, you would need to add a new table. In the single-table design, you would need to add new nullable fields to the user
table. Which is easier to maintain is really up to you, but I personally prefer the table-per-type design because in my uses adding a table is relatively trivial and I dislike intentionally adding fields that I know will contain NULL
'values' without serious optimization advantages (that don't exist in my case, but might in yours).
See also How do you effectively model inheritance in a database?, and/or search for "inheritance" under the database tag for further information.