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:
- Storage Room
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?
\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.
Originally posted by marhaena
Perhaps this will clarify it.
Office has these information:
- Parking Space
Each of those information has its own data/attribute, for example:
Attribute of Land :
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:
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:
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!