Table Structure for Product -> Book
-
Hi there, I have a generic class called Product that has general properties that each product will have. Then I have a class called Book, and Computer. This was easy to do in C# code, but now I have to create the table structures. I'm not sure what is the best way to do it? I was thinking of creating a table called Product, and a table called Book. And then have a product identifier in the Book table to reference the Product table? What will the relation be between these 2 tables? 1:1? Can someone please give me some direction as to what to do and what is the best way of doing it? Regards Brendan
-
Hi there, I have a generic class called Product that has general properties that each product will have. Then I have a class called Book, and Computer. This was easy to do in C# code, but now I have to create the table structures. I'm not sure what is the best way to do it? I was thinking of creating a table called Product, and a table called Book. And then have a product identifier in the Book table to reference the Product table? What will the relation be between these 2 tables? 1:1? Can someone please give me some direction as to what to do and what is the best way of doing it? Regards Brendan
Book should not be a table, it is a TYPE of product (as is Computer). If you go down the path of having a table for each product type (because each product type has different attributes after all) then you will get your self into a maintenance (and data) nightmare. Have a product table with all the general attributes as you have done, add a large description field and see if that will meet your needs. IF you absolutely MUST have additional attributes you might consider an attribute type and attribute table but this is going to add a level of complexity that will make support difficult. There are entire books written and consultants make fortunes writing product structures so don't be scared to experiment.
Never underestimate the power of human stupidity RAH
-
Book should not be a table, it is a TYPE of product (as is Computer). If you go down the path of having a table for each product type (because each product type has different attributes after all) then you will get your self into a maintenance (and data) nightmare. Have a product table with all the general attributes as you have done, add a large description field and see if that will meet your needs. IF you absolutely MUST have additional attributes you might consider an attribute type and attribute table but this is going to add a level of complexity that will make support difficult. There are entire books written and consultants make fortunes writing product structures so don't be scared to experiment.
Never underestimate the power of human stupidity RAH
Thanks for the reply. Book was just an example. There will be other product types as well. Like computers, clothes, etc etc. So I think it is a little impractical to have all the attributes in one table. Or what do you think? What product structures are you talking of? Give some examples?
-
Thanks for the reply. Book was just an example. There will be other product types as well. Like computers, clothes, etc etc. So I think it is a little impractical to have all the attributes in one table. Or what do you think? What product structures are you talking of? Give some examples?
The structure should be something like this (as Mycroft suggested):
ProductType ' (Book, Manchester, Cookware, ...)
ID INT IDENTITY(1,1) PRIMARY KEY,
Name VarChar(50) UNIQUE NOT NULLProductCategory ' (Fiction, NonFiction, Sheets, Pillow Cases, Pots, Pans,...)
ID INT IDENTITY(1,1) PRIMARY KEY,
ProductType_ID INT NOT NULL FOREIGN KEY REFERENCES ProductType(ID),
Name VarChar(50)Product
ID INT IDENTITY(1,1) PRIMARY KEY,
ProductCategory_ID INT NOT NULL FOREIGN KEY REFERENCES ProductCategory(ID),
Name VarChar(50) NOT NULL
...
...To add additional attributes, extend this to Properties...
ProductProperty ' (Price, Dimension, Volume, ...)
ID INT IDENTITY(1,) PRIMARY KEY
ProductType_ID INT NOT NULL FOREIGN KEY REFERENCES ProductType(ID),
Name VarChar(50) NOT NULL,
UOM VarChar(20) NOT NULL,
Description VarChar(50)ProductPropertyValue
ID INT IDENTITY(1,1) PRIMARY KEY,
ProductProperty_ID INT NOT NULL FOREIGN KEY REFERENCES ProductProperty(ID),
Product_ID INT NOT NULL FOREIGN KEY REFERENCES Product(ID)
Value VarChar(MAX)Wire these together with Foreign Keys and you are away - As Mycroft said, this adds aonother layer of complexity.
I don't speak Idiot - please talk slowly and clearly I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury Driven to the arms of Heineken by the wife
modified on Thursday, October 1, 2009 6:28 AM
-
The structure should be something like this (as Mycroft suggested):
ProductType ' (Book, Manchester, Cookware, ...)
ID INT IDENTITY(1,1) PRIMARY KEY,
Name VarChar(50) UNIQUE NOT NULLProductCategory ' (Fiction, NonFiction, Sheets, Pillow Cases, Pots, Pans,...)
ID INT IDENTITY(1,1) PRIMARY KEY,
ProductType_ID INT NOT NULL FOREIGN KEY REFERENCES ProductType(ID),
Name VarChar(50)Product
ID INT IDENTITY(1,1) PRIMARY KEY,
ProductCategory_ID INT NOT NULL FOREIGN KEY REFERENCES ProductCategory(ID),
Name VarChar(50) NOT NULL
...
...To add additional attributes, extend this to Properties...
ProductProperty ' (Price, Dimension, Volume, ...)
ID INT IDENTITY(1,) PRIMARY KEY
ProductType_ID INT NOT NULL FOREIGN KEY REFERENCES ProductType(ID),
Name VarChar(50) NOT NULL,
UOM VarChar(20) NOT NULL,
Description VarChar(50)ProductPropertyValue
ID INT IDENTITY(1,1) PRIMARY KEY,
ProductProperty_ID INT NOT NULL FOREIGN KEY REFERENCES ProductProperty(ID),
Product_ID INT NOT NULL FOREIGN KEY REFERENCES Product(ID)
Value VarChar(MAX)Wire these together with Foreign Keys and you are away - As Mycroft said, this adds aonother layer of complexity.
I don't speak Idiot - please talk slowly and clearly I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury Driven to the arms of Heineken by the wife
modified on Thursday, October 1, 2009 6:28 AM
Thanks, it makes a lot more sense now. I'm just confused to your product category and your product type. Could you explain a little more please? Lets say I have a book product. Product category would be like religion and auto biographies, and product type would be book? Why do you have the following ProductType ' (Price, Dimension, Volume,...) Shouldn't price be generalised into the product table?
-
Thanks, it makes a lot more sense now. I'm just confused to your product category and your product type. Could you explain a little more please? Lets say I have a book product. Product category would be like religion and auto biographies, and product type would be book? Why do you have the following ProductType ' (Price, Dimension, Volume,...) Shouldn't price be generalised into the product table?
OK, ProductType would be Book, ProductCategory would be NonFiction Product: ProductCategory would be NonFiction Name would be Book Title You could then select from your tables as so:
SELECT * FROM Product p
JOIN ProductCategory pc
ON p.ProductCategory_ID = pc.ID
JOIN ProductType pt
ON pc.ProductType_ID = pt.ID
WHERE pt.Name = 'Book'this gives you much flexibility selecting by Type, Category and Name _etc... Hope this makes things clearer. Create a dummy DB in SSMS and generate a Database diagram - the structure will become clearer.
I don't speak Idiot - please talk slowly and clearly I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury Driven to the arms of Heineken by the wife
modified on Thursday, October 1, 2009 6:27 AM
_