Data Modeling: Type + SubTypes

Suppose I have a database that includes 5 different "types" of people, and
each person could simultaneously be of multiple types (the types don't
really matter... so pretend it has doctors, lawyers, janitors, students, and
customers).
My Question: In general, is it perfectly acceptable in a scenario like this
to have one People table for common attributes and then 5 additional "person
type-specific" tables (e.g., a Doctors table, Lawyers, Janitors, Students,
and Customers tables) that each hold type-specific attributes?
Or is there a *general* recommendation to go with 5 tables total and
duplicate the common attributes amongst the tables.
Please understand my objective is to come up with a solid relational
database design - NOT simply to emulate the object-oriented programming
concept of inheritance in my database; and yes, I do understand the issues
related to choice of primary key (my plan is to use an IDENTITY property to
glue the People table with the various "person type" tables, and then have a
UNIQUE constraint on the "real" natural keys per "person type").
Thanks!"Jefffff" <A@.B.COM> wrote in message
news:edGY0teUGHA.2704@.tk2msftngp13.phx.gbl...
> Suppose I have a database that includes 5 different "types" of people, and
> each person could simultaneously be of multiple types (the types don't
> really matter... so pretend it has doctors, lawyers, janitors, students,
> and customers).
> My Question: In general, is it perfectly acceptable in a scenario like
> this to have one People table for common attributes and then 5 additional
> "person type-specific" tables (e.g., a Doctors table, Lawyers, Janitors,
> Students, and Customers tables) that each hold type-specific attributes?
> Or is there a *general* recommendation to go with 5 tables total and
> duplicate the common attributes amongst the tables.
> Please understand my objective is to come up with a solid relational
> database design - NOT simply to emulate the object-oriented programming
> concept of inheritance in my database; and yes, I do understand the issues
> related to choice of primary key (my plan is to use an IDENTITY property
> to glue the People table with the various "person type" tables, and then
> have a UNIQUE constraint on the "real" natural keys per "person type").
> Thanks!
>
>
Your first suggestion is better. The following article explains how and why
tables should not duplicate each other (more precisely, their predicates
should not overlap):
http://www.dbdebunk.com/page/page/622331.htm
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Why do I find that amusing :)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23CQjX2eUGHA.1564@.TK2MSFTNGP11.phx.gbl...
> http://www.dbdebunk.com|||Think about it this way and it might be clearer:
People are people and have attributes that describe them as a person (Name,
DOB, etc)
People can play different roles such as Doctor, Lawyer, etc
So, you would likely have a table named Person to store those attributes
that are about the person
Then, seperate tables for each role the person may play
"Jefffff" <A@.B.COM> wrote in message
news:edGY0teUGHA.2704@.tk2msftngp13.phx.gbl...
> Suppose I have a database that includes 5 different "types" of people, and
> each person could simultaneously be of multiple types (the types don't
> really matter... so pretend it has doctors, lawyers, janitors, students,
> and customers).
> My Question: In general, is it perfectly acceptable in a scenario like
> this to have one People table for common attributes and then 5 additional
> "person type-specific" tables (e.g., a Doctors table, Lawyers, Janitors,
> Students, and Customers tables) that each hold type-specific attributes?
> Or is there a *general* recommendation to go with 5 tables total and
> duplicate the common attributes amongst the tables.
> Please understand my objective is to come up with a solid relational
> database design - NOT simply to emulate the object-oriented programming
> concept of inheritance in my database; and yes, I do understand the issues
> related to choice of primary key (my plan is to use an IDENTITY property
> to glue the People table with the various "person type" tables, and then
> have a UNIQUE constraint on the "real" natural keys per "person type").
> Thanks!
>
>