CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
Nick
Nick

Reputation: 7525

How would I design this table in SQL?

I have a parent master table that is generic enough to hold the common information of the children. Since the children were substantially different, we created separate tables for them. So I have something like this:

tblMaster
--------
MasterID int
Name varchar(50) --Common to all children and there are a bunch of fields like this
ChildType int -- Type of Child either ChildOne or ChildTwo
ChildID int -- need to store ChildOneID or ChildTwoID depending on type of Child, so that i can refer to children

tblChild1
--------
ChildOneID int IDENTITY

tblChild2
---------
ChildTwoID int IDENTITY

Should I have a ChildID in the master that is either ChildOneID or ChildTwoID based on the ChildType column? I have a number of children and I have simplified it for this question.

The other way is to add ChildOneID and ChildTwoID as columns in the master, but since i have a number of columns, I will have a lot of null columns

EDIT: Any help is appreciated

Upvotes: 0

Views: 165

Answers (3)

Jafin
Jafin

Reputation: 4381

Can you just have.

tblMaster
--------
MasterID int
Name varchar(50) --Common to all children and there are a bunch of fields like this

tblChild1
--------
ChildOneID int IDENTITY
MasterID int

tblChild2
---------
ChildTwoID int IDENTITY
MasterID int

?

Assuming each child has one parent, otherwise setup a join table. if its many to many.

Upvotes: 1

automatic
automatic

Reputation: 2737

I would put the identity in the id of the master, then use that same id in the appropriate child table as I show below.

tblMaster

ID int Identity
Name varchar(50) --Common to all children and there are a bunch of fields like this
ChildType int -- Type of Child either ChildOne or ChildTwo

tblChild1

ID int

tblChild2

ID int

Upvotes: 1

Manoj
Manoj

Reputation: 5612

Adding columns per child is bad design, as you need to keep changing the table design as and when a new child types comes. Use foreign key reference of the master table id in the child table

Upvotes: 0

Related Questions