I have two person type: Users and Clients.
Users can login the panel but clients can’t login the panel.
My current database: (but this design not good for me. I need to connect these tables to the Persons table.)
How can I connect these tables to Persons table.
- Users can login with email and password.
- Client can’t login but have email for contact.
I would be happy, If you answer it with a database diagram.
simply, use only one table with a column isclient, which determines if you can log in.
Or if you have lots of more columns for a client a column clientid, which is Null if user is no Client and else use the unique id to the table with clientmoreinformation.
Generally don’t have redundant columns or tables.
Generally from what I’ve seen is to ask if a client can be a user, and if a user can be a client. If the two lists do not overlap, then it is perfectly fine to treat them as two different entities. It would be no different than having a table for salespeople and a different table for sales inventory. Just because a computer desk has height and weight attributes, doesn’t make it a user like me.
If in the event that there is a lot of overlap between the lists, like if you were looking at regular members of a club and premium members, then it would be very appropriate to have a couple of additional columns in a single table that shows their status.
If you needed to reference an event that had both a user and a client, then a join table with foreign keys could be used to link them together.
This is an instance of exclusive subtypes. Read this SO answer for an in-depth treatment of the topic, I will only address the example provided in your question.
You are correct that your current design has flaws, namely there’s redundancy, and the relation (a person is either a
client OR a
user, but not both) is not clear or enforced.
So it does make more sense to have a
Person entity with two subtypes,
User. Since the only difference between the two is a
User has a
Password, that is the only additional information stored with
Client exists to enforce any relations specific to
Clients and not
It’s important the the relations between
Person are enforced through foreign keys. In MySQL, the exclusive subtype will need to be enforced through triggers.
CREATE TABLE PersonType ( PersonTypeCd CHAR(1) NOT NULL ,Name VARCHAR(50) NOT NULL ,CONSTRAINT PK_PersonType PRIMARY KEY (PersonTypeCd) ,CONSTRAINT AK_PersonType UNIQUE (Name) ) ; CREATE TABLE Person ( PersonId INT NOT NULL ,PersonTypeCd CHAR(1) NOT NULL ,Email VARCHAR(64) NOT NULL /* Everything else */ ,CONSTRAINT FK_Person_Classified_By_PersonType FOREIGN KEY (PersonTypeCd) REFERENCES PersonType (PersonTypeCd) ,CONSTRAINT PK_Person PRIMARY KEY (PersonId) ,CONSTRAINT AK_Person UNIQUE (Email) ) ; CREATE TABLE User ( UserId INT NOT NULL ,Password VARCHAR(128) NOT NULL ,Salt VARCHAR(8) NOT NULL ,CONSTRAINT FK_User_Is_Person FOREIGN KEY (UserId) REFERENCES Person (PersonId) ,CONSTRAINT PK_User PRIMARY KEY (UserId) ) ; CREATE TABLE Client ( ClientId INT NOT NULL ,CONSTRAINT FK_Client_Is_Person FOREIGN KEY (ClientId) REFERENCES Person (PersonId) ,CONSTRAINT PK_Client PRIMARY KEY (ClientId) ) ;
These are the changes I made to your example:
- Identifiers require context, so
ClientIdin the parent tables, not just child tables.
- Email should be unique and enforced through constraints.
- Having three timestamps is unnecessary and doesn’t provide much use in terms of recording changes. Have one timestamp for when the record was updated (that includes creating) and store the prior versions of the data in an audit table.
- Passwords shouldn’t be encrypted without a salt
Other things you may consider:
- It’s possible for people to have more than one phone, so it’s worth considering modeling an entity
PersonPhonewith the primary key
PhoneNbr)in case you will start collecting more than one phone number in the future.
- If you will store more information than just the password for
User, you will probably want an audit table for that entity as well.