Phil Parkin (12/2/2016)
drew.allen (12/2/2016)
Phil Parkin (12/2/2016)
Just having a mind warp on how to insert into the address table and get that unique int value and put it into the correct master row for the employee.
This relationship sounds the wrong way round to me. Wouldn't this make more sense?
Employee(EmployeeId, etc etc)
Address(AddressId, EmployeeId, AddLine1, etc etc)
Actually, people and addresses form a many-to-many relationship, so a normalized solution would have a third table that contained at least the person_id, address_id, and probably start and end dates.
Having an address id on a person (employee) table indicates that this is something like the primary address. Having a person id on an address table indicates that that person is something like the head of household.
Drew
I agree, but have not seen it implemented as a many-to-many in the types of system I work with. I'd model a 'primary' address using a flag on the Address table, rather than having to hit the Employee table.
The degree to which this is normalized depends on the requirements of the application. Taking it to an extreme, I once maintained a proprietary EMPI database (Enterprise Master Person Index) which was leveraged as part of an ETL process to match up a population of 40,000,000 distinct Persons to raw data feeds originating daily from 100s of 3rd party sources. So, the Person table contained essentially just a PersonID, and then there was a snowflake type data model with 10s of billions of time stamped many-to-many relationships on 20+ demographic attributes (first name, last name, dob, address, email, phone, SSN, etc.). It had to not only accommodate all variations of name usages, but also multiple address formatting schemes, partial dates of birth, family members with the same first+last name, sex changes, and assign a rule based confidence score for each match. That was an interesting challenge from a support perspective.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho