1

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

My database:

You can see these tables storing same data like: firstName, lastName... but the difference is that the customer does not have a password. enter image description here

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.

5
  • Are there relations within the database that should only exist for clients or users?
    – user212533
    Dec 3, 2020 at 15:50
  • ..Yes, there is Dec 3, 2020 at 15:56
  • And there are users who are clients or are users always clients?
    – user212533
    Dec 3, 2020 at 16:08
  • Clients can't be user, user can't be client. Dec 3, 2020 at 16:14
  • Start with this SO question: stackoverflow.com/questions/4896831/… If you need additional detail/clarification I'll do my best to provide. With MySQL you'll have to use triggers to enforce the subtype exclusivity.
    – user212533
    Dec 3, 2020 at 16:37

3 Answers 3

1

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, Client and User. Since the only difference between the two is a User has a Password, that is the only additional information stored with User. Client exists to enforce any relations specific to Clients and not Users.

ER Diagram

Since you requested a diagram, here is a logical model of the solution, with a few tweaks I will explain: enter image description here

Physical implementation

It's important the the relations between Client and User to 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)
)
;

Other considerations

These are the changes I made to your example:

  1. Identifiers require context, so PersonId,UserId and ClientId in the parent tables, not just child tables.
  2. Email should be unique and enforced through constraints.
  3. 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.
  4. Passwords shouldn't be encrypted without a salt

Other things you may consider:

  1. It's possible for people to have more than one phone, so it's worth considering modeling an entity PersonPhone with the primary key (PersonId,PhoneNbr) in case you will start collecting more than one phone number in the future.
  2. If you will store more information than just the password for User, you will probably want an audit table for that entity as well.
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.