I would consider a few things. If all types contain similar information,
a single table solution makes it simpler to manage, you don't have to
worry that you forget to update the table for some types if you change
something. If each type is very different to the others, so that keeping
the information in one table would mean that many fields were irrelevant
for a particular type, I'd say it could be cumbersome to keep track of
which field belonged to a particular type unless you used separate
tables. Another thing to consider, could be the number of tables in the
database, a database containing hundreds of tables is more difficult to
comprehend than one containing ten.

Oh, and don't forget foreign keys. If one type of record conceptually
has a foreign key to another table, whereas another type doesn't, then
this would be difficult/impossible to implement using a single table. On
the other hand, if a record in another table conceptually has a foreign
key to either of the types, but it could vary which type, then a single
table or (separate tables + a combined table with the keys) would be
your options.

So, my overall advice is to consider your own situation, I don't think
there is any general answer saying that one of the possibilities is
preferable in all cases.

HTH,
Set

markd_mms wrote:

> I've been tasked with merging our internal DVD, classical music and
> general music databases (currently stored in 3 separate MSSQL
> databases) into one DB.
>
> In the new design title information for all 3 types is stored in the
> PRODUCT table and release information per title is stored in the
> PRODUCT_DETAIL table. Classical products have more information at the
> title level, so that is stored in the CLASSICAL_PRODUCT table (which
> is 1:1 with PRODUCT).
>
> I need a way of being able to select from the PRODUCT table based on
> type, and rather than adding a "type" column to PRODUCT (e.g. 1 = DVD,
> 2 = general music, 3 = classical) I thought considering I already have
> a CLASSICAL_PRODUCT table why don't I just add a DVD_PRODUCT and
> MUSIC_PRODUCT table that only list their product ID's so I can select
> based on type but using a join instead.
>
> Is there any advantage doing it one way over the other?
>
> TIA