August 27, 2015 at 8:37 am
Hi there,
I am trying to get a databse going and I have a qusetion:
I have some items that I have to work with, and most of them have similar proprieties, yet there are some properties that apply only to some. for example:
1. table of items would have:
id, ser#, dateofPurchase, cost.....etc.
Items with special(extra) properities:
a) Monitor would have everything + size , type, connector type...etc
c) Cell phone same + IMEI, OS, ... etc.
and so On.
I have identified About 6 different items groups that have add-on properities, and my question is how would I design table or tables?
Thanks
September 3, 2015 at 10:16 am
whenever I am faced with design decisions, I seem to get a bit of confidence by making a list of options. Have you looked at the various ways you could do this?
You could add enough columns to cover everything, and then just leave them NULL for the cases that won't apply to that piece of equipment.
On the other hand, I'm a little biased toward making one of the (sets of) column(s) into an entity attribute sort of thing, this way you won't be burdened by adding new columns for stuff you haven't thought of or made allowances for, but I've also had experience working with this sort of construct and while its not an ideal fit for relational databases (according to some 🙂 ), it does seem to work!
September 3, 2015 at 12:17 pm
Thank you for reply. One mega table is not an option because there would be a lots of nulls in each row.
September 3, 2015 at 12:55 pm
vceklic (9/3/2015)
Thank you for reply. One mega table is not an option because there would be a lots of nulls in each row.
That would be my concern also! Thats why I mentioned the "entity attribute" thing.
The first table would have the fixed set of columns common to all the parts.
The second table would then have a foreign key pointing to the first table, with rows for each special characteristic that also include this key that points to the first table.
Another way to go would be to make secondary tables with columns that only apply to certain types, like your master table could be "equipment_master", with the id as primary key, then for each row in that equipment_master table that was a cell phone, you could then have a "cell_phone" table with attributes specific to cell phones.
The disadvantage then would be that you'd have to have a secondary table for each class of equipment and that gets unwieldy. Thats why my personal choice for this situation would again be the "entity attribute" table as the secondary table, with a row for each attribute that has a column for the equipment id, a column that would "name" the attribute, and a column that would contain the attributes value, its much more flexible and maintainable, but on the other hand, it introduces extra work in querying for the information both for the coder and the database server.
https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model has a better discussion of the data model.
September 3, 2015 at 2:30 pm
Can you subclass the entities? Essentially, the "main" table has an autoincrement primary key, and the "child"/subclass tables just inherit that key from Main. There's a really simple example of subclassing (using Access) on http://access.mvps.org/access/tables/tbl0013.htm
Rebecca does a really simple example, but you could expand on that idea.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply