none
How do i create a Supertype substype relationship in SQL?

    Question

  • I have a Personnel Registration table that is the supertype to the 3 subtype table vendor, sponsor, volunteer.  How do i link the tables in t-sql? I know this is probably not the proper way to do this but i know it can be done. Any suggestions or comments would be appreciated.
    Monday, November 12, 2012 5:56 PM

Answers

  • Hi bvance,

    I suggest storing all the super type attributes in one table, and in other tables, please add a foreign key to references the super type table. For example:

    create table Person
    (
    	Person_ID int primary key,
    	Name varchar(100),
    	-- add some other common attributes (all of vendor, sponsor, volunteer have) here.
    )
    
    create table vendor
    (
    	vendor_ID int primary key,
    	Person_ID int foreign key references Person(Person_ID)
    	-- add some other attributes related to vendor here.
    )
    
    create table sponsor
    (
    	sponsor_ID int primary key,
    	Person_ID int foreign key references Person(Person_ID)
    	-- add some other attributes related to sponsor here.
    )
    
    create table volunteer
    (
    	volunteer_ID int primary key,
    	Person_ID int foreign key references Person(Person_ID)
    	-- add some other attributes related to volunteer here.
    )
    

    Additionally, if you want to use your original table design, please provide more detail information about your tables design and some sample data here.



    Allen Li

    TechNet Community Support

    Tuesday, November 13, 2012 7:02 AM
  • Hi ,

    Using joining you can do it .

    http://www.codeproject.com/Articles/102805/SQL-Joins


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Thursday, November 15, 2012 2:47 PM

All replies

  • I suggest that you review your naming convention, as it may reveal a potential problem.  It seems to me that you may have missed the real supertype.  Typically, "registration" is something associated with the supertype but does not represent the supertype itself.  However, it may work for your purposes. 

    Using your example, then the next question is the actual relationship (or relationships) to be modelled.  Presumably a registration cannot be both vendor and sponsor (or any other combination)?  If so, you simply propagate the PK key of registration (supertype) into the subtype tables.  Prevention of multiple subtypes can be accomplished via triggers, indexed views, or just assumed.  So you get something like:

    Registration:  registration_id (identity, pk), ...
    Vendor: registration_id (pk), ...
    Sponsor:  registration_id (pk), ...
    etc.

    The link Below has a more extensive discussion.

    http://www.sqlmag.com/article/tsql3/sql-by-design-supertypes-and-subtypes

    Monday, November 12, 2012 7:28 PM
  • Hi bvance,

    I suggest storing all the super type attributes in one table, and in other tables, please add a foreign key to references the super type table. For example:

    create table Person
    (
    	Person_ID int primary key,
    	Name varchar(100),
    	-- add some other common attributes (all of vendor, sponsor, volunteer have) here.
    )
    
    create table vendor
    (
    	vendor_ID int primary key,
    	Person_ID int foreign key references Person(Person_ID)
    	-- add some other attributes related to vendor here.
    )
    
    create table sponsor
    (
    	sponsor_ID int primary key,
    	Person_ID int foreign key references Person(Person_ID)
    	-- add some other attributes related to sponsor here.
    )
    
    create table volunteer
    (
    	volunteer_ID int primary key,
    	Person_ID int foreign key references Person(Person_ID)
    	-- add some other attributes related to volunteer here.
    )
    

    Additionally, if you want to use your original table design, please provide more detail information about your tables design and some sample data here.



    Allen Li

    TechNet Community Support

    Tuesday, November 13, 2012 7:02 AM
  • Hi ,

    Using joining you can do it .

    http://www.codeproject.com/Articles/102805/SQL-Joins


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Thursday, November 15, 2012 2:47 PM