How is inheritence like data best done in SQL

[wackyphill]

If you have several entities that have many common properties but a few
have a few unique fields to them how do you design your tables?

DO you make a seperate table for each entity even though they have many
common fields or is there a way to do an OO type thing where you have a
common table for all and somehow tack on the unique fields?

Just unsure whats possible and what's best.

Thanks for any input.

[Steve Jorgensen]

On 18 Oct 2005 08:03:59 -0700, wa********@yahoo.com wrote:

If you have several entities that have many common properties but a few
have a few unique fields to them how do you design your tables?

DO you make a seperate table for each entity even though they have many
common fields or is there a way to do an OO type thing where you have a
common table for all and somehow tack on the unique fields?

Just unsure whats possible and what's best.

Thanks for any input.

The standard way I've always seen and often do is to have a "base" table with
the common fields, and a 1-to-1 relationship to tables with fields for the
specific case. There's even a symbol for this used on database diagrams.

Here's an example

address
address_id
country
country_subdivision
city
postal_code

street_address
address_id
street_name
street_number

postal_address
address_id
postal_box

Every address has an "address", and every address will have either a
"street_address" or a "postal_address", but not both.

[wackyphill]

Ok, so is the idea is to remember to always do outer joins w/ the
address table to get all the info available?

[Steve Jorgensen]

On 18 Oct 2005 08:36:39 -0700, wa********@yahoo.com wrote:

Ok, so is the idea is to remember to always do outer joins w/ the
address table to get all the info available?

Once you have the structure, there are lots of options for how to retrive data
from it. An outer join to each and every "child" table is one option, or you
can add an address type column, and have the client do a second query to
retrieve the details of an address from the appropriate place.

[Erland Sommarskog]

(wa********@yahoo.com) writes:

If you have several entities that have many common properties but a few
have a few unique fields to them how do you design your tables?

DO you make a seperate table for each entity even though they have many
common fields or is there a way to do an OO type thing where you have a
common table for all and somehow tack on the unique fields?

Just unsure whats possible and what's best.

Basically as Steve says.

One has to be a little careful, and not overdo it. If it's only one or
two extra columns, maybe it's better to keep them in the main table.
Or let several "subclasses" share a table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Steve Jorgensen]

On Tue, 18 Oct 2005 22:06:57 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.se> wrote:

(wa********@yahoo.com) writes:

If you have several entities that have many common properties but a few
have a few unique fields to them how do you design your tables?

DO you make a seperate table for each entity even though they have many
common fields or is there a way to do an OO type thing where you have a
common table for all and somehow tack on the unique fields?

Just unsure whats possible and what's best.

Basically as Steve says.

One has to be a little careful, and not overdo it. If it's only one or
two extra columns, maybe it's better to keep them in the main table.
Or let several "subclasses" share a table.

I concure with that. My example, in fact, is a case where having 3 tables
instead of optional fields is usually overkill.