design question : the sub-entities cohesion problem

Hello,
I am in the process of designing a big commercial database for a
wholesaler.
Since many months now, I have been asking myself questions about how to
physically implement a system of tables that would efficiently
represent the logical situation of many "sub-entities" related to a
single "master entity".
Here is a simplified example :
A company has a catalogue of many products, grouped in 3 Categories :
toys products, books products and CDs products.
We agree that each of the categories should have its own physical
table, since CDs are different from books, etc.
We also agree that all categories share similar informations such as a
price, a name, a purchase date, etc. ... this legitimate the creation
of a single "products" table. With that table, we will also manage the
products IDs, since we dont want duplicate among categories.
Therefore, we have 4 tables to create : Products, Products_Books,
Products_Toys and Products_CDs.
The unique ID of a product is hold in the Products table. The
sub-tables hold category-oriented information. Each sub-tables refer
to the master table with a many-to-1 FK.
First problem : how can I physically make sure that a product exist in
only one sub-table? Of course, this is managed at the application
level ... but how can I put a database-constraint to physically avoid a
situation where a product entry from the Products table would have both
a sub-entry in the book AND the toy sub-table.
Second problem : how can I physically make sure that all products in
the master table have 1 entry in a sub-table. Or, if you prefer, how
can I make sure that the FK between the master and the sub-entities is
a genuine 1-to-many relationship and NOT a 0.1-to-many relationship.
All answers will be appreciate.
thank you,
BTGGoogle around for someof my postings on modeling classes in SQL.|||before.the.gods@.gmail.com wrote:
> First problem : how can I physically make sure that a product exist in
> only one sub-table? Of course, this is managed at the application
> level ... but how can I put a database-constraint to physically avoid a
> situation where a product entry from the Products table would have both
> a sub-entry in the book AND the toy sub-table.
>
CREATE TABLE products (sku_code CHAR(13) NOT NULL PRIMARY KEY,
product_type CHAR(1) NOT NULL CHECK (product_type IN ('B','A','T' /*
Books, Audio, Toys */)), product_name VARCHAR(50) NOT NULL UNIQUE, /*
... attributes common to all product types */ UNIQUE (product_type,
sku_code));
CREATE TABLE books (sku_code CHAR(13) NOT NULL PRIMARY KEY,
product_type CHAR(1) NOT NULL DEFAULT 'B' CHECK (product_type = 'B'),
FOREIGN KEY (product_type, sku_code) REFERENCES products (product_type,
sku_code));
CREATE TABLE audio (sku_code CHAR(13) NOT NULL PRIMARY KEY,
product_type CHAR(1) NOT NULL DEFAULT 'A' CHECK (product_type = 'A'),
FOREIGN KEY (product_type, sku_code) REFERENCES products (product_type,
sku_code));
CREATE TABLE toys (sku_code CHAR(13) NOT NULL PRIMARY KEY, product_type
CHAR(1) NOT NULL DEFAULT 'T' CHECK (product_type = 'T'), FOREIGN KEY
(product_type, sku_code) REFERENCES products (product_type, sku_code));

> Second problem : how can I physically make sure that all products in
> the master table have 1 entry in a sub-table. Or, if you prefer, how
> can I make sure that the FK between the master and the sub-entities is
> a genuine 1-to-many relationship and NOT a 0.1-to-many relationship.
>
Populate the parent and the referencing table in the same proc. You can
use a trigger to prevent deletes that violate the rule but 1-1
cardinality is hard to enforce through constraints because SQL Server
doesn't support deferrable constraints.
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
--|||Store the common columns in each sub-entity table, and then create a view
that is the union of the common columns. You'll need triggers on all of the
sub-entity tables to prevent duplicate entries and if you want to be able to
modify the product view, then you'll need instead of triggers on it.
<before.the.gods@.gmail.com> wrote in message
news:1138998544.545064.58550@.g14g2000cwa.googlegroups.com...
> Hello,
> I am in the process of designing a big commercial database for a
> wholesaler.
> Since many months now, I have been asking myself questions about how to
> physically implement a system of tables that would efficiently
> represent the logical situation of many "sub-entities" related to a
> single "master entity".
> Here is a simplified example :
> A company has a catalogue of many products, grouped in 3 Categories :
> toys products, books products and CDs products.
> We agree that each of the categories should have its own physical
> table, since CDs are different from books, etc.
> We also agree that all categories share similar informations such as a
> price, a name, a purchase date, etc. ... this legitimate the creation
> of a single "products" table. With that table, we will also manage the
> products IDs, since we dont want duplicate among categories.
> Therefore, we have 4 tables to create : Products, Products_Books,
> Products_Toys and Products_CDs.
> The unique ID of a product is hold in the Products table. The
> sub-tables hold category-oriented information. Each sub-tables refer
> to the master table with a many-to-1 FK.
> First problem : how can I physically make sure that a product exist in
> only one sub-table? Of course, this is managed at the application
> level ... but how can I put a database-constraint to physically avoid a
> situation where a product entry from the Products table would have both
> a sub-entry in the book AND the toy sub-table.
> Second problem : how can I physically make sure that all products in
> the master table have 1 entry in a sub-table. Or, if you prefer, how
> can I make sure that the FK between the master and the sub-entities is
> a genuine 1-to-many relationship and NOT a 0.1-to-many relationship.
> All answers will be appreciate.
> thank you,
> BTG
>|||Splitting the 3 categories (toys, book, CDs) into 3 seperate tables is a
denormalized design and should be avoided. Are you sure that their
attributes are so different that they would need to be contained in seperate
tables? Doing so will make your SQL queries more complex, and what if the
business decides later that it really needs 4 categories instead? That's a
major revision to both the database model and also the application
programming.
Assuming you still need to go with that design, then you can implement this
by storing the 3 foreign keys in the Products table. Conceptually, I see
this as a relationship with 3 entirely different entities (or so you say)
but with the addition of a rule that a Product can only be related to one of
these entities at a time.
The following foreign key relationships insure that only valid categories
are assigned:
Products.ProductsToysID smallint -> Products_Toys.ProductsToysID
Products.ProductsBooksID smallint -> Products_Books.ProductsBooksID
Products.ProductsCDsID smallint -> Products_CDs.ProductsCDsID
The following check constraint insures that one and only one category is
specified:
(
coalesce(ProductsToysID,ProductsBooksID,
ProductsCDsID) is not null
)
and
(
coalesce(ProductsToysID,ProductsBooksID,
ProductsCDsID) =
isnull(ProductsToysID,0) + isnull(ProductsBooksID,0) +
isnull(ProductsCDsID,0)
)
That should handle the data integrity part.
Below is an example of how you would join product category name with a
product list:
select
P.Name as ProductName,
P.Price as ProductPrice,
coalesce(T.Name,B.Name,C.name) as ProductCategoryName
from
Products as P
left join Products_Toys as T
on T.ProductsToysID = P.ProductsToysID
left join Products_Books as B
on B.ProductsBooksID = P.ProductsBooksID
left join Products_CDs as C
on C.ProductsCDsID = P.ProductsCDsID
You may be able to simplify things by making creative use of Views.
<before.the.gods@.gmail.com> wrote in message
news:1138998544.545064.58550@.g14g2000cwa.googlegroups.com...
> Hello,
> I am in the process of designing a big commercial database for a
> wholesaler.
> Since many months now, I have been asking myself questions about how to
> physically implement a system of tables that would efficiently
> represent the logical situation of many "sub-entities" related to a
> single "master entity".
> Here is a simplified example :
> A company has a catalogue of many products, grouped in 3 Categories :
> toys products, books products and CDs products.
> We agree that each of the categories should have its own physical
> table, since CDs are different from books, etc.
> We also agree that all categories share similar informations such as a
> price, a name, a purchase date, etc. ... this legitimate the creation
> of a single "products" table. With that table, we will also manage the
> products IDs, since we dont want duplicate among categories.
> Therefore, we have 4 tables to create : Products, Products_Books,
> Products_Toys and Products_CDs.
> The unique ID of a product is hold in the Products table. The
> sub-tables hold category-oriented information. Each sub-tables refer
> to the master table with a many-to-1 FK.
> First problem : how can I physically make sure that a product exist in
> only one sub-table? Of course, this is managed at the application
> level ... but how can I put a database-constraint to physically avoid a
> situation where a product entry from the Products table would have both
> a sub-entry in the book AND the toy sub-table.
> Second problem : how can I physically make sure that all products in
> the master table have 1 entry in a sub-table. Or, if you prefer, how
> can I make sure that the FK between the master and the sub-entities is
> a genuine 1-to-many relationship and NOT a 0.1-to-many relationship.
> All answers will be appreciate.
> thank you,
> BTG
>|||> Splitting the 3 categories (toys, book, CDs) into 3 seperate tables is a
> denormalized design and should be avoided.
No it isn't. And no it shouldn't (always be avoided). See David's post for
the proper structure of a sub-typed relationship. It actually makes things
quite nice, since columns that they have in common are right there for the
searching (name, productType, etc) and then you can get more specific in the
child table (the specific tables.) The Product table would have the primary
key and it would be migrated to the children.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <someone@.microsoft.com> wrote in message
news:e98WE0QKGHA.516@.TK2MSFTNGP15.phx.gbl...
> Splitting the 3 categories (toys, book, CDs) into 3 seperate tables is a
> denormalized design and should be avoided. Are you sure that their
> attributes are so different that they would need to be contained in
> seperate tables? Doing so will make your SQL queries more complex, and
> what if the business decides later that it really needs 4 categories
> instead? That's a major revision to both the database model and also the
> application programming.
> Assuming you still need to go with that design, then you can implement
> this by storing the 3 foreign keys in the Products table. Conceptually, I
> see this as a relationship with 3 entirely different entities (or so you
> say) but with the addition of a rule that a Product can only be related to
> one of these entities at a time.
> The following foreign key relationships insure that only valid categories
> are assigned:
> Products.ProductsToysID smallint -> Products_Toys.ProductsToysID
> Products.ProductsBooksID smallint ->
> Products_Books.ProductsBooksID
> Products.ProductsCDsID smallint -> Products_CDs.ProductsCDsID
> The following check constraint insures that one and only one category is
> specified:
> (
> coalesce(ProductsToysID,ProductsBooksID,
ProductsCDsID) is not null
> )
> and
> (
> coalesce(ProductsToysID,ProductsBooksID,
ProductsCDsID) =
> isnull(ProductsToysID,0) + isnull(ProductsBooksID,0) +
> isnull(ProductsCDsID,0)
> )
> That should handle the data integrity part.
> Below is an example of how you would join product category name with a
> product list:
> select
> P.Name as ProductName,
> P.Price as ProductPrice,
> coalesce(T.Name,B.Name,C.name) as ProductCategoryName
> from
> Products as P
> left join Products_Toys as T
> on T.ProductsToysID = P.ProductsToysID
> left join Products_Books as B
> on B.ProductsBooksID = P.ProductsBooksID
> left join Products_CDs as C
> on C.ProductsCDsID = P.ProductsCDsID
> You may be able to simplify things by making creative use of Views.
>
> <before.the.gods@.gmail.com> wrote in message
> news:1138998544.545064.58550@.g14g2000cwa.googlegroups.com...
>