I am having a bit of a brain freeze. I have a table whose properties are the root of four distinct elements, for example:
- I need to relate
SystemCommon, where one
SystemCommoncan have only one of the subtypes. How do I do this?
Should I add a
SubType table and then add a
SubTypeID to each
CREATE TABLE SystemCommon ( ID int identity(1,1) not null, Name varchar(20) not null, Descriptor varchar(20) not null, CommonInfo varchar(20) not null CONSTRAINT PK_Parent Primary Key CLUSTERED (ID) ) CREATE TABLE SystemType1 ( ID int identity(1,1) not null, UniqueSystem1Field varchar(100) not null CONSTRAINT PK_SystemType1 Primary Key CLUSTERED (ID) ) CREATE TABLE SystemType2 ( ID int identity(1,1) not null, UniqueSystem2Field varchar(100) not null CONSTRAINT PK_SystemType2 Primary Key CLUSTERED (ID) ) CREATE TABLE SystemType3 ( ID int identity(1,1) not null, UniqueSystem3Field varchar(100) not null CONSTRAINT PK_SystemType3 Primary Key CLUSTERED (ID) )
Clarifications for comment requests:
Perhaps "Parent" is really a poor choice of wording, and "SystemCommon" might be better.
SystemCommonis a table that contains columns that are common to all
SystemTypesare the unique parameters for each of the
SystemTypeis its own table. There are four
SystemCommoncan have only one
SystemType(any 1 of the 4).
SystemCommonscan have the same
SystemType1can belong to
I am trying to model accordingly. A main group of elements that correlates to a given system (just for example as it is the best analogy I can come up with)
A computer has a
Name and an
IpAddress (all computers -
SystemCommon). Each of these computers can have program[x1] or a program [x2] or a program[x3] but it can have only one of those program types, but it can have as many instances of that program type as it likes (so this is a one-to-many relationship); the hard part is to relate which
SystemType (x1, or x2 or x3). It seems like a case of inheritance, as suggested via comments.