1

enter image description here

I want to know should I remove PersonTypeId from Persons table or is it a good idea to have it in there?

New contributor
Hamid Noahdi is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2

You need it there so that you know what PersonType is Person is. Without it, you don’t know.

Normalisation means that you don’t have PersonTypeName in the Persons table. But PersonTypeID is fine and what you need.

  • @rob_farley When we need Customers we can join Persons table by Customers and get all customers without needing PersonTypeId in Persons table. Despite being so helpful having PersonTypeId in Persons table, I am not sure about performance and integrity of data – Hamid Noahdi Mar 24 at 11:13
  • Oh, is PersonTypeID telling you that a particular person is a Customer or a Driver or a Seller? In that case, scrap the PersonType table completely. Otherwise you can’t have a person be a Driver and a Seller, only one. – Rob Farley Mar 25 at 3:41
  • @rob_farley you pointed to a very important point. What if I want a person just have one role. Do you think in this situation PersonTypes table isn't helpful. I think by having PersonTypeId I can get persons faster just by a WHERE clause, but without it I have to join tables each time i need a specific person. But I am not sure about if it is a Bad Practice. – Hamid Noahdi Mar 25 at 4:35
  • I think I’d be tempted to just have three tables, and scrap the Persons table. But it depends on how many columns you have in Persons. If it’s just Name, just include that in each of the others. – Rob Farley Mar 25 at 11:50
0

In addition to Rob Farley's answer, a common pattern to guarantee that PersonTypeId is correct, is to introduce a superkey in PERSONS:

ALTER TABLE Persons ADD CONSTRAINT ... 
    UNIQUE (PersonTypeId, Id);

ALTER TABLE Persons ADD CONSTRAINT ...   
    CHECK ( PersonTypeId in ('C','D','S'));

and extend each of the sub-types with the type attribute. Now Customers can reference this super-key:

CREATE TABLE Customers
( CustomerID ... NOT NULL -- is this needed?
, PersonId ... NOT NULL
, PersonTypeId CHAR(1) DEFAULT 'C' NOT NULL
, ...
,     CHECK ( PersonTypeId = 'C' )
,     FOREIGN KEY ( PersonTypeId, PersonId)
         REFERENCES Persons (PersonTypeId, Id)
);

A couple of DBMS (Sybase?) allow SELECT in CHECK constraints. It is then not necessary to add the attribute in the sub-tables. Most DBMS don't allow this though, so it is fairly common to do like above.

Your Answer

Hamid Noahdi is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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