defining tables relationship

Posted on 2012-04-04
Medium Priority
Last Modified: 2012-06-21

I need to relate 3 tables e.g. prod, series, sub-series and noticed that when I am using composite primary key it cannot be incremental. This is my plan and I will appreciate it if you give me some help:

table 1: prod     has prod_id (PK) and desc
table 2: series   has prod_id(FK and PK) and series_id (PK)
tables 3: sub-series  series_id (FK and PK) and sub_series(PK)

if this is a right approach, how can I make prod_id and series_id incremental ? and same thing about sub_series table?

if not, then what you suggest?

Thanks in advance,
Question by:akohan
LVL 18

Accepted Solution

lludden earned 800 total points
ID: 37807347
CREATE TABLE Prod (ProdKey INT IDENTITY(1,1) --Other fields)
CREATE TABLE Series (SeriesID INT IDENTITY(1,1), ProdKey int) --ProdKey is a FK to Prod Table
CREATE TABLE SubSeries(SubSeriesID int IDENTITY(1,1), SeriesID int) SeriesID is FK to Series table

Each Prod can have multiple series, but each series can only have 1 prod
Each Series can have multiple subSeries, but each sub-series can only belong to a single series.

If you want to allow multiple series to have multiple products (a many-to-many relationship), then you do it this way:
CREATE TABLE Prod (ProdKey INT IDENTITY(1,1) --Other fields)
CREATE TABLE Series (SeriesID INT IDENTITY(1,1) --Other fields)
CREATE TABLE ProductSeries( ProdKey int, SeriesID int) --Both Prodkey and seriesID make a composite PK

and to do the same with subseries
CREATE TABLE SubSeries(SubSeriesID int IDENTITY(1,1) --Other fields)
CREATE TABLE SeriesSubSeries(SeriesID int, SubSeriesID int) --Both seriesID and SubseriesID make a composite PK

Author Closing Comment

ID: 37825698

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question