page 1 of 1
Comments: 2 | Views: 693
#Oct 9th @ 1:55 PM
qwert231
qwert231
M Kenyon
We'd like to move the addresses into a common address table. We have employees and customers.

So, Let's say we have our normal address table.

Our employee table has fields like this:
Address1
Address2
City
State
Zip

We add a column to the employee table called AddressID

Can I do something like this:
UPDATE employee SET AddressID =
(INSERT INTO Adresses (Address1,...) VALUES (employee.Address1,...)
RETURN @@IDENTITY)

Would that work? Or would it be better to create a function that takes the address params and returns the Identity and do something like this:
UPDATE employee SET AddressID = udf_InsertAddress(employee.Address1,...)
#Oct 9th @ 8:14 PM
figuerres
figuerres
???
sounds to me like a case of "over normalizing"

what is the percived benefit of doing this?

has the cost been looked at?

for example to display / edit  a customer or an employee you would now have a join to show the data.
that's a lot of joins.....

and how do you manage the relations -- you are creating an entity that can be linked to two different entities and may have to be split into two for some reasons.....

and what percent of the employees are also customers, how many times ?

also in some cases there  might be a legal issue -- some staff in sales may not need to know the personal data on say female staff for example....  like home address.

and what happens if a rep / clerk etc... edits the wrong customer record and chnages the address of a staff member.... where does the pay check get mailed to??

so what are the benefits, what are the possible costs ?
#Oct 9th @ 10:50 PM
TommyCarlier
TommyCarlier
Trust me, I'm from the Internets

No matter whether this is the right thing to do or not, here might be a possible solution: split it in 2 SQL-statements: first insert all the Address-records, then update the Employee-records:

INSERT INTO Addresses (Address1, ...)
SELECT Address1, ... FROM Employee


UPDATE Employee SET AddressID = a.AddressID
FROM Employee AS e 
JOIN Address AS a ON e.Address1 = a.Address1 AND ...

page 1 of 1
Comments: 2 | Views: 693