August 15, 2013 at 9:16 am
Hello.
I am working on a database where we have designed a generic "Addresses" table. We have many classes of records where each type might have multiple addresses.
So for example, classes of records could be Salesman. District Manager. Store. Remote Office. Etc. All fictitious but you get the idea. Each record could have one more many addresses so we came up with the universal Address table.
I am starting to write test queries to get the data out. The example below does work but I have to think there's a more efficient way to do this and would appreciate feedback.
LookUpID is the ID of the record from any given table. Could be SalesPersons. Could be Retail Location. Could be a Investor. Etc.
Eventually LookUpID will given a parameter but right now, it's hard coded just to test with.
RecordTypeID identifies what table to look in.
So in this example, the record in the Addresses Table we are testing against belongs to a SalesPerson. So the logic is, the SalesPerson (or whoever) data would be loaded into the form of the application. We already know the ID of the record whatever type it is. That's the LookUpID.
SELECT SalesPersonID, FirstName, LastName,
(SELECT Address1 FROM dbo.Addresses WHERE (LookUpID = 1) AND (RecordTypeID = 3)) AS Address1,
(SELECT Address2 FROM dbo.Addresses AS Addresses_2 WHERE (LookUpID = 1) AND (RecordTypeID = 3)) AS Address2,
(SELECT City FROM dbo.Addresses AS Addresses_3 WHERE (LookUpID = 1) AND (RecordTypeID = 3)) AS City,
...etc....
FROM dbo.SalesPersons
RecordTypes
---------------
1 - Owner
2 - District Manager
3 - SalesPerson
4- etc....
Thanks for the feedback.
August 15, 2013 at 9:21 am
A simple JOIN should work.
DECLARE @LookUpID int
SELECT sp.SalesPersonID,
sp.FirstName,
sp.LastName,
a.Address1,
a.Address2,
a.City,
...etc....
FROM dbo.SalesPersons sp
--LEFT?
JOIN dbo.Addresses a ON sp.SalesPersonID = a.LookUpID AND a.RecordTypeID = 3
WHERE sp.SalesPersonID = @LookUpID
August 15, 2013 at 9:25 am
The performance would be appalling. Consider Luis' approach, or even APPLY():
SELECT SalesPersonID, FirstName, LastName,
x.Address1,
x.Address2,
x.City,
...etc....
FROM dbo.SalesPersons
OUTER APPLY (
SELECT Address1, Address2, City
FROM dbo.Addresses
WHERE (LookUpID = 1)
AND (RecordTypeID = 3)
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 15, 2013 at 9:27 am
I'll give it a shot. I could tell the performance would suffer by embedding all those selects.....
August 15, 2013 at 9:45 am
This appears to be a significant design flaw.
The first question is would address be considered an entity, or is address really an attribute of Other entities? :Salesman has address" would be the verb phrase. Not "address has salesman or district manager or..". In this case, there should probably be a table to store the addresses for each entity. You could argue that this is repeating data because "123 Main St" is contained over and over, but you can also argue that it is not repeated because it is associated with many different entities.
Think of a name. Many people can have the name "Michael", but we don't normally create a table of first names, and try to create a relationship between the first name table and many other tables in a database.
The second question is, if address is indeed an entity on it's own, then lookupid and record type should probably not be in the address table.
These should be moved into a separate set of tables that resolve the many to many relationship between the address table and the rest of the tables.
So, you will have the address table, the salesman, district manager etc. etc. tables, and a separate table such as Salesman_Address that contains the primary key of the salesman, the primary key of the address, and if required, an address type lookup.
Sorry for all of the theory but I just spent the better part of this morning diagnosing, tuning and patching a really bad query based upon a really bad design that is identical to your design. This table, as well as any code that uses it, have been one of the biggest sources of deadlocks and bottlenecks in the system. I would not want to wish the work required to remove this design on my worst enemies!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 16, 2013 at 6:34 am
Thanks for the input Michael. I'll try to refine this design and come back in a couple hours and post the results.
August 16, 2013 at 6:44 am
In addition to the comment above, you can also have the situation that your sales person is also a customer - what do you do then? this is why you need a bridge table.
Your entities in one table with an EntityID as a unique key
Your address table with an addressID as a unique key
The bridge which would look like (e.g.)
(possibly have a surrogate key such as EntityAddressID)
EntityID
AddressID
AddressTypeID
(I would also add EffectiveDate (i.e. you may enter future addresses but the sales person, customer has not 'moved in' yet), it also allows you to keep history; add Status (Active, Inactive); etc etc)
I hope this makes sense,
B
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply