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 SystemType to a SystemCommon, where one SystemCommon can have only one of the subtypes. How do I do this?

Should I add a SubType table and then add a SubTypeID to each SystemType?

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:

  1. Perhaps "Parent" is really a poor choice of wording, and "SystemCommon" might be better.

    a. SystemCommon is a table that contains columns that are common to all SystemTypes.

    b. SystemTypes are the unique parameters for each of the SystemTypes - each SystemType is its own table. There are four Types currently.

    c. A SystemCommon can have only one SystemType (any 1 of the 4).

    d. Many SystemCommons can have the same SystemType. So SystemType1 can belong to SystemCommon (where id=1..n).

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.

  • 1
    Still not entirely clear. Shall we assume that a row in Parent (SystemCommon) be related to many rows in a child table (SystemTypeX) but all theses children should be in the same X? Feb 5 '16 at 18:19
  • @ypercube A column of a row in SystemCommon (Parent) needs to refer to the specific SystemType Table [Which Table] AND Which Row of that Specific table.
    – StixO
    Feb 5 '16 at 18:23
  • 1
    I was expecting a Yes (to many rows) or No (only to one row) answer. Feb 5 '16 at 18:24
  • 1
    @StixO: If you are struggling with making your verbal description perfectly clear and unambiguous, perhaps you could update your question with examples (data samples) illustrating what is and what isn't possible in the set-up you are trying to implement.
    – Andriy M
    Feb 5 '16 at 20:36
  • @Andriy M : case of inheritance - yes much like that. Thanks for jogging my mind I should have included that wording..
    – StixO
    Feb 8 '16 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.