One table with "TypeID" column or multiple tables.....?

  • Hi all,

    we've got new functionality going into our systems soon and i'm just looking for some words of wisdom. As an example of what i'm trying to achieve, say we have an insurance system which will obviously hold all the insurance info for all types of insurance products. Now, we are looking to create some seperate tables for each product to hold additional information that is not generic to this insurance table.

    The question i have is, do i create separate tables (potentially 5-6) for each product to hold this additional info or do i create one with a producttypeID? Baring in mind that in the near future these tables could expand and not all columns could be relevant to all products so could have several unused cells in the table if put them all into one.

    I'm trying to enforce a policy (developers being a PITA) of having as few NULL columns as possibly for performance issues (index scanning as opposed to seeks on NULL's) so ideally don't want a load of columns with NULL values in - hence the separate tables.......

    any recommendations??

    Cheers

    _________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

  • I really believe that creating separate tables for individual products will cause you more headaches than it will solve. The options you could take are:

    1.) The most flexible thing would be to create an attribute table, that only has a few columns in it, a product ID foreign keyed to the product table itself, an ID or name of the attribute, and the value of the attribute. This would allow various products to have any number of different attributes, but could become a performance issue depending on the number of attributes that each product has and how many products total there are. Developers will also have to think "vertically" (many rows) instead of "horizontally" (many columns in 1 row) to read the attributes into the application for displaying.

    2.) If there are different product categories or types that are used only in certain parts of the application you could consider a separate table for each of those. You'll probably want to keep the number of tables accessed from any one screen in the application low, because if the application has any screens that display more than one product at a time or products of various types, then the developers will have to either code the queries to look at all of the different attribute tables, even if the product only truely belongs to one of them, or code the queries in such a way that there are different queries for different product types and you'll have to face redundant code and some odd UNION ALL clauses in the queries.

    3.) Come up with a compromize on the rule of having too many NULL values in columns. I'm not sure what the performance issue is with having NULL values in a table, but if there truely is a performance degredation then maybe this isn't the choice for you.

    Each way has its pros and cons, and will require some pain either for the developers or you.

  • As stated already, you could consider the attribute/value method. I have used the EAV style model before for this type of issue and entire systems. I would say be careful with it as well. It can cause performance problems, difficult queries, difficulties in enforcing rules at the database level, etc.

    I would consider using a base table to hold all the common elements currently known. Then when/if new items are added to can include them as sub tables. For example we have a Customer table that holds generic columns. Then we have CustomerX or CustomerY that holds specific data for that client that is unique to them (similar to your other plan).

    No solution is going to be a silver bullet, I would talk to the developers and see impacts on code and application and see if they have a preference as well.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply