One to Many Relationship in Databse design

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

One to Many Relationship in Databse design

Author

Topic

csri
Starting Member

45 Posts

Posted - 2009-03-25 : 18:37:26

Dear All
I have a question on one-Many Relationship in database design.I have two tables products and Featuredproducts.
Products table columns are
(productId is the primary key and other columns are catId,Name,Price)

The FeaturedProducts table has two columns (text and saleprice)

I think there is a one-Many Relationship between products and Featuredproducts.If it is , can I make the primary key of products table primary key in Featuredproducts.or Do I need to make primary key
of products table a foreign key in featuredproducts and add a new column and make it a primary key.which one is correct when there is a one-Many Relationship between products and Featuredproducts.

Featuredproducts table columns are
ProductId primary key
Text
saleprice.

Is it correct.when will the primary key column of one table becomes
primary key in another table in a 1-Many Relationship.Please somebody clarify my doubt.I am designing database for the first time.If any thing wrong please correct me.Really appreciate your answer.

Thanks and Regards
Sri

jeffcj
Starting Member

2 Posts

Posted - 2009-03-27 : 05:16:12

Just a few thoughts that may help you.

You need to have a full understanding of the relationship between the Products table and the FeaturedProducts table. Think ahead to how your "application" will be used.

Will the features products change? If so, will you ever need historical tracking of what products were featured products at a given time?

For example, given the scenario above, your FeaturedProducts table might look like:

FeaturedProduct_pk (primary key)
Product_ID (FK related to Products table)
Feature_Start (datetime)
Feature_End (datetime)

Jeff

dportas
Yak Posting Veteran

53 Posts

Posted - 2009-03-28 : 12:53:31

Duplicate post. Here's my reply from the other thread:

This looks more like an optional 1-1 relationship rather than 1-many. I would expect your FeaturedProducts to look something like this:

CREATE TABLE FeaturedProducts
(ProductId INT NOT NULL REFERENCES Products (ProductId),
Txt VARCHAR(MAX) NOT NULL,
SalePrice NUMERIC(10,2) NOT NULL);

"Text" is a poor choice for a column name because it's a reserved word.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources