Professional Web Applications Themes

Common Columns - Microsoft SQL / MS SQL Server

I am designing a database for an asset management system. The assets are pieces of equipment, some of which are components for other pieces of equipment. The requirements state that there is a set of common columns for each piece of equipment, e.g. value. A suggested database design is a table for each piece of equipment with columns specific to that piece of equipment and another table containing the columns common to all pieces of equipment. Each equipment-specific table would have a foreign key to the common table, establishing a one- to-one relationship. One of the suggested common columns is ...

  1. #1

    Default Common Columns

    I am designing a database for an asset management system.
    The assets are pieces of equipment, some of which are
    components for other pieces of equipment.

    The requirements state that there is a set of common
    columns for each piece of equipment, e.g. value. A
    suggested database design is a table for each piece of
    equipment with columns specific to that piece of equipment
    and another table containing the columns common to all
    pieces of equipment. Each equipment-specific table would
    have a foreign key to the common table, establishing a one-
    to-one relationship. One of the suggested common columns
    is equipment type, which appears to be equivalent to the
    identity of the equipment-specific table. This would
    facilitate checks on the columns in the common table
    according to the equipment type, e.g. NULL or NOT NULL.

    The alternative to the design above is simply to include
    the common columns in each equipment-specific table.

    I feel uneasy about using a common table for the following
    reasons:

    1. I very much doubt some of the suggested common columns
    really are common; I suspect NULL will put in a frequent
    appearance!

    2. Equipment type is only required to facilitate checking
    the columns in the common table. It points towards the
    use of a single Equipment table for all pieces of
    equipment!

    3. Is there any reason for using a common table, other
    than time saved creating the equipment-specific tables?

    4. Whenever a new column is required, there will need to
    be a debate about whether it is common or not!

    Does anybody have any experience using a common table in
    this way? If so, would you recommend it?

    Can anybody think of a good reason to use a common table,
    or not to use one?

    Perhaps there isn't much between the two approaches!

    Thanks for any advice.
    Leo Guest

  2. #2

    Default Re: Common Columns

    You say that you doubt is the common columns really are common. Regardless
    of how you split the columns you will still end up with loads of NULL fields
    if the common columns aren't... common...!

    I woudl certainly not suggest using a single table for all equipment types.
    While this may appear to be appealing short term, as more equipment and
    specific data columns are added you'll end up with a messy table, not to
    mention the possibility of exceeding the 8K record limit imposed on table
    rows.

    If a new column is required - it should be pretty obvious if its common.
    ie. if you add a 'date entered' column then it's obviously common to all
    products, but a column 'NoOfFlangesOnLeftSide' could be only relevant to a
    specific product.

    So, use a common table, and link to the appropriate equipment table
    depending on the type.

    Bear in mind where you're using dynamic table names you'll have to
    dynamically create the SQL and, maybe insert into a temporary holding table
    for reference. Also, any reports required on the entire dataset can only
    include the common columns...

    Hope it's been some help!

    Cheers,
    Dave

    "Leo Bateman" <nospam.com> wrote in message
    news:0b0001c35bfc$8f4b7880$gbl... 


    Dave Guest

  3. #3

    Default Re: Common Columns

    Hi Leo,

    The following 2 articles are probably worth a read, as they explain
    super/subtyping (which is the "official" name of the concept we are talking
    about) in more detail:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag03/
    html/HowManysTooMany.asp
    http://www.sqlmag.com/ InstantDoc #5226

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Leo Bateman" <nospam.com> wrote in message
    news:093b01c35c13$c2f36190$gbl... 
    > common. Regardless 
    > loads of NULL fields 
    > all equipment types. 
    > equipment and 
    > messy table, not to 
    > imposed on table 
    > if its common. 
    > obviously common to all 
    > only relevant to a 
    > equipment table 
    > you'll have to 
    > temporary holding table 
    > dataset can only 
    > message [/ref]
    > system. [/ref]
    > equipment [/ref]
    > would [/ref]
    > one- [/ref]
    > columns [/ref]
    > following [/ref]
    > columns [/ref]
    > checking [/ref]
    > table, 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Jacco Guest

Similar Threads

  1. Columns and Inherited Datagrid...Active Schema does not support columns
    By rob thomson in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 4th, 03:09 PM
  2. Replies: 0
    Last Post: July 2nd, 06:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139