defining tables relationship

Hello,

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,
ak
akohanAsked:
Who is Participating?
 
lluddenConnect With a Mentor Commented:
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
0
 
akohanAuthor Commented:
Thanks!
0
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.

All Courses

From novice to tech pro — start learning today.