December 2, 2016 at 9:35 am
i.e.
MasterTable:
"Emloyee table"
first_name
last_name
home_address_uno
Business_address_uno
Detail table:
"Address Table"
AddressUno = unique int value stored in either employee_table.home_address_uno or employee_table.business_address_uno
AddressType = home/business etc. corresponging to the address stored.
AddressLine1
AddressLine2
AddressLine3
AddressLine4
City
state
Zipcode
Country
The process would be looping through a Dataset and inserting rows into the above tables.
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.
-I'm sure this is easier then I think. (yes, I may have imposter syndrome)
Thanks!
December 2, 2016 at 10:14 am
You could always just add a column to the addresses table to indicate what type it is (home or work). Then you would just write a case statement to get the related address you wanted.
Why do you need to track the insert into the detail table at all?
December 2, 2016 at 10:18 am
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)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 2, 2016 at 10:34 am
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)
+10000
OP, consider your architecture. If you need to add a new address type to an employee you would have to change your table to include yet another column with a foreign key to the address table. This defeats the point.
If you use the type of architecture that Phil is suggesting you simply add a new row to the address table (and probably one to the table of AddressTypes).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2016 at 10:35 am
Assuming that AddressUno is an IDENTITY column this will be a lot easier to manage.
First insert the Address table for home address, business address, etc. and for each use the SCOPE_IDENTITY() function to store into variables the identity value inserted. Next, when inserting Employee, use the same previously stored IDs.
INSERT INTO Address (AddressType,...) VALUES ('home',...);
SELECT @HomeAddressUno = SCOPE_IDENTITY();
INSERT INTO Address (AddressType,...) VALUES ('business',...)
SELECT @BusinessAddressUno = SCOPE_IDENTITY();
INSERT INTO Emloyee ( ..., home_address_uno, Business_address_uno )
VALUES ( ..., @HomeAddressUno, @BusinessAddressUno );
Another issue is what to do when the Address already exists. Address is a separate entity from Employee, and it would break proper normalization to duplicate addresses for employees who have resided at the same address or have the same business address. So you want to identify each Address using a natural key on something like StreetAddress + ZipCode and then re-use that address rather than re-inserting it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 2, 2016 at 10:45 am
That would definitely be easier. This is the first time out for me designing a database.
I was also thinking about making a table for countries and states allowing me to use an int for those in the master employee table.
I was also thinking about this address table.
There will only ever be x business addresses x = the total number of offices (I was trying to reduce having them in the master table)
however after typing that out i think creating an office table where the pirmary key is it's office number which will be stored in the master will solve that.
December 2, 2016 at 10:46 am
Eric M Russell (12/2/2016)
Another issue is what to do when the Address already exists. Address is a separate entity from Employee, and it would break proper normalization to duplicate addresses for employees who have resided at the same address or have the same business address. So you want to identify each Address using a natural key on something like StreetAddress + ZipCode and then re-use that address rather than re-inserting it.
My 2¢ is that addresses are one of those places where you can seriously over normalize your data. It can quickly become a logistical nightmare to keep addresses in synch like you are suggesting. There is a point where you have to accept some level of denormalized information and for me addresses are one of those. Sort of like zip codes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 2, 2016 at 11:04 am
Sean Lange (12/2/2016)
Eric M Russell (12/2/2016)
Another issue is what to do when the Address already exists. Address is a separate entity from Employee, and it would break proper normalization to duplicate addresses for employees who have resided at the same address or have the same business address. So you want to identify each Address using a natural key on something like StreetAddress + ZipCode and then re-use that address rather than re-inserting it.
My 2¢ is that addresses are one of those places where you can seriously over normalize your data. It can quickly become a logistical nightmare to keep addresses in synch like you are suggesting. There is a point where you have to accept some level of denormalized information and for me addresses are one of those. Sort of like zip codes.
What I provided above was just a quick 2¢.
Ideally, the application would leverage a 3rd party like USPS, LexisNexis or MelissaData to acquire a standardized master Addresses, Phone, Person, etc. database that covers whatever geographic regions their clients originate from. When entering an address for a new client they will drill down on a specific AddressID using partial search and information provided by the client. This way they wouldn't be creating new addresses ad-hoc, or at least 99.9% of the addresses would be conformed. Another advantage is that this conformed AddressID can be used to integrate their application database with other internal or external databases that uses the same master address source. In many large organization, especially eCommerce, there are IT staff dedicated just to the task of maintaining these master databases that source applications across the enterprise.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 2, 2016 at 11:24 am
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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 2, 2016 at 11:50 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 2, 2016 at 12:23 pm
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply