Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
Table-per-type with one-to-many relationship

Answered Table-per-type with one-to-many relationship

  • Wednesday, June 20, 2012 8:14 AM
     
     
    Sign In to Vote
    0

    I'm currently looking into TPT as a potential solution for a project I'm currently working on but I have to also factor in multiple languages for the system I'm developing and I'm not sure if I'm headed in the right direction.

    Using TPT I have a Product table with Projector and Screen tables part of the inheritance heirarchy. The system I need to develop will also have to deal with regional copies of products (so there could be a Projector in French, German and English, there could be another Projector in Spanish and French only etc, and the same applies to Screens as well). 

    A potential solution I've found is to create additional tables under each of my sub product tables (see my diagram) e.g. "ProjectorRegion" where a one-to-many relationship is then established between Projector table and the ProjectorRegion table and all the Projector-only fields I would normally place in Projector now go into ProjectorRegion table instead. This allows for identifying a particular regional version of a projector or screen by specifying the generic product ID and the region_ID.

    This design concerns me that I could be going about this the wrong way because it essentially reduces each sub table (Projector/Screen) down to only containing an ID that exists in the generic Product table anyway, making those tables into a joining table between Product and ProjectorRegion and ScreenRegion.

    Is there a better way to design this schema?

     

All Replies

  • Wednesday, June 20, 2012 9:19 AM
    Answerer
     
     
    Sign In to Vote
    1

    I have  couple of questions to you

    1) Is there any relation between Projector and Screen tables?

    2) Do you have  Region table and have a RegionID (as you probably have) in Projector_Region table?

    Generally your design looks ok....Why do you think it reduce sub table?

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

     
  • Wednesday, June 20, 2012 10:29 AM
     
     
    Sign In to Vote
    0

    Hi Uri,

    Thanks for your reply; to answer your questions:

    1. There are several reasons why I opted for a relationship between projector and screen into a generic Product table; I will be creating a Category class and linking projectors and screens with this (a product can have 1 or more categories, so therefore I will use a CategoryProduct joining table between Product and Category)... the generic product table helps to alleviate any issues I'd have with regards to foreign keys otherwise (i.e. how would I tell my CategoryProduct join table that an ID I've fed it refers to projector "x" with the ID "1" or screen "x" with the ID "1"; by using TPT I remove the issue of colliding IDs between the projector and screen tables in this respect.

      I also intend to use this pattern with Entity Framework and my system can therefore use abstraction; I define Product and can then assign a concrete implementation of either Projector or Screen.

    2. Yes I have a Region table with an "ID" PK, and ProjectorRegion has the FK you identified.

    I wondered if there was a more efficient way to do it, considering that for each type of product I need to create an additional two tables. I have shortened my example for brevity, but we actually have five kinds of product inc projectors and screens, so in total I'd be creating ten tables + the generic product table just for storage of products.

     
  • Wednesday, June 20, 2012 10:37 AM
    Answerer
     
     
    Sign In to Vote
    1

    >>>considering that for each type of product I need to create an additional 

    Is it possible to have TypeID in Projectors and Screen tables? Moreover , I would  be looking into one table contains 

    TypeID,ProjectorID,ScreenID..

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

     
  • Wednesday, June 20, 2012 11:05 AM
     
     
    Sign In to Vote
    0

    Do you mean as a replacement to my "Projector" and "Screen" tables I have a table that instead has TypeID, ProjectorID, ScreenID and then that is the table that binds ProjectorRegion, ScreenRegion and Product tables together? I.e something like:


     
  • Wednesday, June 20, 2012 11:16 AM
    Answerer
     
     Answered
    Sign In to Vote
    1

    Ok, good, now I would have one Region table and probably addition column that describes relation to Projector or Screen or Accessory...Sure, that ProductType table will have only one RegionID column, have you considered this?

    CREATE TABLE Regions

    (

    RegionID INT,

    IntorText NVARCHAR(MAX),

    VersionID INT,

    RegionType CHAR(1)  ---P (Projectors),S (Screens),A(Accessory)

    )

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

     
  • Wednesday, June 20, 2012 11:43 AM
     
     
    Sign In to Vote
    0

    Hi Uri,

    Do you mean a table that could replace the three existing region tables? If so, how would I deal with the varying spec fields across tables, e.g:


     
  • Wednesday, June 20, 2012 11:49 AM
    Answerer
     
     
    Sign In to Vote
    0

    Well, one option is to have all fields but some of them will allow NULL for irrelevant Region. I am just trying to eliminate having three tables , tomorrow it could be more, right? Moreover, having one table  you reduce number of JOIN to get the data and etc... 


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

     
  • Wednesday, June 20, 2012 12:04 PM
     
     
    Sign In to Vote
    0

    Yes that's true, and the simplicity of one table is compelling, but with one table I'm concerned that I'll end up with one table with hundreds of columns, many of which will be null (for example, we only have 10 accessories but we have > 100 projectors, but accessories has around 20 distinct columns that projectors wouldn't use)... we also have another range of products which have around 60 spec columns but there is only two of those products.

    I did fleetingly think about an EAV design to solve this (one table for attributes, one table for values, one table for the product ID + name and one table which specifies "types" of product) but I concluded that this "database within a database" design is not good for a relational DB or for Entity Framework which is what I intend to use, plus for the loss in performance and difficulty in constructing SQL statements to get to the data, it would not be worth the hassle.

    My thinking with separate tables is, whilst you're right that tomorrow there could be another product range and another table, the chances of this aren't high; my company are a projector manufacturer, so we specialise only in a subset of products and this system is not an e-commerce platform where the types of products we're adding are unknown at the time of the system and DB development.

     
  • Wednesday, June 20, 2012 12:09 PM
    Answerer
     
     
    Sign In to Vote
    0
    How large that database is going to be? 100GB? 800GB? Can you predict that?

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

     
  • Wednesday, June 20, 2012 12:24 PM
     
     
    Sign In to Vote
    0

    Hi Uri,

    I can give it a go; our current DB is around 2 1/2 GB in size, and it grows an half GB every 2 years, around about... our DB is currently a "super DB" that contains everything our website does, not just storing product info but also stuff like support forms from our website etc, all our intranet etc (I inherited that unfortunately!). So it's a very small database; we only really deal in projectors and closely-related products, we're a mid-sized company and we probably have around 700 products all in.

     
  • Wednesday, June 20, 2012 12:30 PM
    Answerer
     
     Answered
    Sign In to Vote
    1

    Hi

    Well,so go for your current design (Projector,Screen and Accessory) .. I hope that properly defined indexes on the table will increase performance of the queries..

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/