Logically model never possible real world or my forever imperfect business?
Date: 6 Mar 2003 09:58:58 -0800
Please help me understand the proper 3rd Normal Form for the following simple Model.
If your new project has to live with old inefficient business rules do
you logically model
(up to third normal form) the business with its limits it will always
live with or
do you data model an ideal world/business that it will never come close to?
When I get to the physical design and more complexities (such as
resolving many to many
and including maintaining history) I'm sure I'll make chooses if needed that will
keep the design to the reality of my business rather than some standardized ideal world.
Just take as an example the real estate transactions recording
business where "we use type
written documents as your input and have data entry people pick out transaction
info. and the system later accumulates it into a datawarehouse". This is very error prone and we
can't enforce all the constraints on all the various levels of human involvment to make
all the data come out perfectly. Its especially true that the historical records can
never be error corrected also. This is why the real estate business has Title Abstract
companies always doing research to ok everything.
Please help me understand whether I should logically model my
business's real world
or the ideal world.
More specifics follow below.
I have a PARCEL (i.e., piece of land) and each PARCEL has one OWNER
(i.e., a person).
Therefore I think I should model my database as having two entities and its in 3rd normal form.
PARCEL OWNER parcel id attribute owner id attributeparcel number attribute ownername attribute
However my data source for parcels and owners comes from type written
and ownername could be coming to me mispelled. The same person may own
several parcels and each time his name could be mispelled differently. Furthermore,
even after this database is constructed I will have no source of cleaning
ownernames up, so as far as the database knows it can not assume one 'JOHN SMITH'
is the same as another or if its the same as 'JON SMITH'.
My question is "For proper logical design and 3rd normal form,
1) is it right to leave the PARCEL and OWNER as separate entities
or can I combine owners into parcels and end up with just one entity,
since ownername are freehand? (I will always have exactly as many
ownernames as parcels).
2) if I leave PARCEL and OWNER as separate and since ownername is not maintained cleanly (therefore there is a one to one relationship between PARCEL and OWNER), should/could I have the owner id U.I. be renamed and identical to the parcel id?
Another way to point out my problem is to think about a Blockbuster
movie rental model.
What if I didn't hand out Customer membership cards (to determine) owners uniquely
and people just signed their names (with errors or short names at times). And several
people have the same name in the same family address so that won't help uniquely
identify the customer
Thanks in advance for any help. Received on Thu Mar 06 2003 - 18:58:58 CET