May 12, 2006 at 4:39 am
Hi,
I have a question regarding data modelling and will try and explain as precisely as possible. We are currently mapping an LDM to the physical data model.
Currently there is an entity which has 7 attributes. If Attribute1 has a particular value e.g. 0, then attributes 2-4 are populated with data and attributes 5-7 cannot contain values (i.e. they are mutually exclusive on these attributes).
Vice versa if attrib1 has e.g. value 1, then attributes 2-4 cannot contain values and attribs 5-7 are populated with data.
This has been mapped by the developer to the physical model as two separate tables. My view is that this is not ideal from a modelling and programming point of view (as there is no way of knowing whether attribute1 value exists in either table without always querying both tables).
My view is that the two tables be combined, and that either when the specific attributes cannot be populated with data, either null or default values are implemented and that check constraints are used to implement the business rule depending upon the value in attribute1 (this can only be 1 of 2 values).
Hope this is clear and would be interested to hear views/thoughts.
Thanks.
Paul R Williams.
May 12, 2006 at 7:35 am
I have to say that i think i agree with your developers.
this seems to be very open to interpretation, but i think that if you have table values that are dependent on a status value then you've breached 3rd normal form...??????
it's a bit like having a table of customers and recording and address type field (work or home) and depending on whether they are businesses or individiuals yo store the address in the respective columns (home_addr1, home_add2 or business_addr1, business addr2)
you wouldn't find a dba doing this in most places - more than likely you'd end up with an attributes table with a foreign key reference back to the main table
for example
table employee contains details about the employee, but table employee_contact contains details about contacting the employee that may be of varying detail.
that way your attribute table stores an attribute id,attribute name, attribute value and foreign key reference to employee.
i'm not saying this is the fastest and most effective use of these tables - you really need to look at queries you'll be running - but i definatley think that a single table with empty columns is poor practice.
MVDBA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply