Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    4

    Red face DB design... HELP...

    I have several catagories and sub catagories to be made into a table, but the data structure of the sub catagories are not all the same; 5 out of 6 catagories have the same structure and the other have a different data structure. My question is, how do i design the database for those catagories?

    For example the catagories are:
    - Office
    - Storage Room
    - Dock

    Each catagory has a sub catagory of "Land", the land in office and storage room has exactly the same data structure but the land in the
    dock catagory has a totaly different structure.

    Do i make each individual tables for each land in each catagory, knowing that there is one data structure that is different or do i combine those that have the same structure and make the other one in a different table?

    Thanks for the help,
    Marhaena H.S

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: DB design... HELP...

    \You have explained quite a lot, but I still don't really understand what you mean by "Land" being a "sub category" of Office. Do you mean a collection of attributes that an Office has (e.g. width, area)? Or do you mean a subtype - some Offices are "Land", some are not? Or something else again?

    Perhaps if you can give some examples it will help.

  3. #3
    Join Date
    Oct 2002
    Posts
    4

    Re: DB design... HELP...

    Perhaps this will clarify it.

    Office has these information:
    - Land
    - Building
    - Drainage
    - Parking Space

    Each of those information has its own data/attribute, for example:

    Attribute of Land :
    - area
    - type
    - elevation

    So... Office, Storage Room, and Dock each has land and the attribute of Land for office and storage room are the same but different from the attribute of Land for Dock.

    What should i do? make one table for each land (office, storage room, and dock) or do i combine the table of land for office and storage, and make another table for the land of Dock?

    Sorry if it's still confusing... I hope this will clarify it...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: DB design... HELP...

    Originally posted by marhaena
    Perhaps this will clarify it.

    Office has these information:
    - Land
    - Building
    - Drainage
    - Parking Space

    Each of those information has its own data/attribute, for example:

    Attribute of Land :
    - area
    - type
    - elevation

    So... Office, Storage Room, and Dock each has land and the attribute of Land for office and storage room are the same but different from the attribute of Land for Dock.

    What should i do? make one table for each land (office, storage room, and dock) or do i combine the table of land for office and storage, and make another table for the land of Dock?

    Sorry if it's still confusing... I hope this will clarify it...
    OK, so "Land" is really a named collection of attributes that an Office and a Storage Room both have. And a Dock also has a collection of attributes that you call "Land", but the attributes are different.

    One option (as you have said) is just to create a table each for Office, Storage Room and Dock:

    Table OFFICE( office_id, ..., land_area, land_type, land_elevation, ... )
    Table STORAGE_ROOM( sr_id, ..., land_area, land_type, land_elevation, ... )
    Table DOCK( dock_id, ..., land_xxx, land_yyy, ... )

    Another option (as you have said) is to combine Office and Storage Room:

    Table OFFICE_SR( office_sr_id, type, ..., land_area, land_type, land_elevation, ... )
    Table DOCK( dock_id, ..., land_xxx, land_yyy, ... )

    OFFICE_SR has an extra "type" column to specify which it is.
    Presumably there are some attributes that an Office has and a Storage Room does not (and vice versa). These will have to be declared as optional columns, possibly with a check constraint to ensure they are populated correctly.

    Yet another option is to pull out the common "Land" information into a separate table:

    Table OFFICE( office_id, ...)
    Table STORAGE_ROOM( sr_id, ...)
    Table LAND( land_id, office_id, sr_id, land_area, land_type, land_elevation)
    Table DOCK( dock_id, ..., land_xxx, land_yyy, ... )

    or:

    Table LAND( land_id, land_area, land_type, land_elevation)
    Table OFFICE( office_id, land_id, ...)
    Table STORAGE_ROOM( sr_id, land_id, ...)
    Table DOCK( dock_id, ..., land_xxx, land_yyy, ... )

    Or you can put ALL the common attributes of Office and Storage Room into a "supertype" table:

    Table OFFICE_SR( osr_id, type, land_area, land_type, land_elevation, ...)
    Table OFFICE( osr_id, ...)
    Table STORAGE_ROOM( osr_id, ...)
    Table DOCK( dock_id, ..., land_xxx, land_yyy, ... )

    Any of these ideas may be suitable, you need to decide what is best for your situation. For example, if there are just 3 "Land" attributes that Office and Storage Room have in common, and they have a number of other attributes not in common, then it may be worth having 2 tables and just repeating the attributes. If they have many other attributes in common also, then perhaps the supertype/subtypes option is better.
    If, as you suggest, there are potentially 5 tables that will have the same "Land" attributes then it may be a good idea to have a LAND table, so that any validation/processing of "Land" data is in one place.

    I guess a reasonable common analogy is People, Companies and Addresses.
    People have Addresses and Companies have Addresses. Addresses have 5-6 attributes. I have seen database designs where the address attributes are repeated: PEOPLE has address_line_1, ... postcode, and so does COMPANIES.
    I have seen other databases where there is an ADDRESS table that is shared by PEOPLE and COMPANIES.
    I have seen yet other databases where there is a supertype table called PARTY that holds common info about People and Companies, including the address attributes.

    I'm afraid that what I am saying is that there is no single correct answer for all situations, you need to weigh up which provides the best solution for your data. I hope that helps!

  5. #5
    Join Date
    Oct 2002
    Posts
    4

    Re: DB design... HELP...

    Wow... thanks for the detailed answer,... it has been a great help. I really appreciate your help.

Posting Permissions

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