September 7, 2011 at 5:21 pm
According to my boss, the design that I created is too complicated, but I think its fine. I was wondering if I could get some direction so that I could present a new design to my boss or convince him that my design is a good one.
The main table in this design is the Customer table. A Customer can have many addresses and many contacts. The contacts and the addresses are broken down by types (Shipping, Billing...)
Based on that description I came up with the following tables:
Customer
CustID is the Primary Key
Address
AddressID is the Primary Key
Contact
ContactID is the Primary Key
TypeCodes
TypeID is the Primary Key
CustomerAddresses
CustID + TypeID + AddressID is the primary Key
CustomerContacts
CustID + TypeID + ContactID is the primary Key
Is this too complicated? Can someone suggest something better? If this is a good design, can someone who has more experience then I do, give me ammunition in order to convince my boss why the design is a good one.
Thanks so much
September 7, 2011 at 5:57 pm
meichner (9/7/2011)
According to my boss, the design that I created is too complicated, but I think its fine. I was wondering if I could get some direction so that I could present a new design to my boss or convince him that my design is a good one.The main table in this design is the Customer table. A Customer can have many addresses and many contacts. The contacts and the addresses are broken down by types (Shipping, Billing...)
Based on that description I came up with the following tables:
Customer
CustID is the Primary Key
Address
AddressID is the Primary Key
Contact
ContactID is the Primary Key
TypeCodes
TypeID is the Primary Key
CustomerAddresses
CustID + TypeID + AddressID is the primary Key
CustomerContacts
CustID + TypeID + ContactID is the primary Key
Is this too complicated? Can someone suggest something better? If this is a good design, can someone who has more experience then I do, give me ammunition in order to convince my boss why the design is a good one.
Thanks so much
I'm hoping those primary keys in contact/addresses are three separate columns, or I'd have to shoot you. 😉
Can a contact belong to more then one customer? No, thus a hierarchy. Same for address. Thus, AddressID and ContactId are perfectly fine as the PK, with CustID and Type as attributes.
Can a contact and an address be both the 'shipping type'. Nope, you'll want two different type tables.
As to why your boss things it's overcomplicated, we're going to need more details. What does he think is 'overcomplicated'? Does he want address1, address2, address3, contact1, contact2, contact3? Besides the incredibly painful structure and difficulty with dealing with the data, why does he think that's 'better'? Is he incredibly weak in T-SQL and think joins are over-complicated?
You need to find out WHY he thinks it's overcomplicated before we can give you ammunition to use. Generic advice in a case like this rarely will help, as you're usually fighting a pre-conceived notion and the wrong tack will just entrench them, thinking "you don't know what you're doing anyway, that's not what I care about".
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 8, 2011 at 9:37 am
Evil Kraig F (9/7/2011)
I'm hoping those primary keys in contact/addresses are three separate columns, or I'd have to shoot you. 😉
Can a contact belong to more then one customer? No, thus a hierarchy. Same for address. Thus, AddressID and ContactId are perfectly fine as the PK, with CustID and Type as attributes.
Can a contact and an address be both the 'shipping type'. Nope, you'll want two different type tables.
As to why your boss things it's overcomplicated, we're going to need more details. What does he think is 'overcomplicated'? Does he want address1, address2, address3, contact1, contact2, contact3? Besides the incredibly painful structure and difficulty with dealing with the data, why does he think that's 'better'? Is he incredibly weak in T-SQL and think joins are over-complicated?
You need to find out WHY he thinks it's overcomplicated before we can give you ammunition to use. Generic advice in a case like this rarely will help, as you're usually fighting a pre-conceived notion and the wrong tack will just entrench them, thinking "you don't know what you're doing anyway, that's not what I care about".
Thank goodness you don't have to shoot me.
Let me explain the relationships a little better.
A Customer can have many Shipping/Billing addresses and many Shipping/Billing contacts.
An Address can belong to more then one Customer or Customer type. In other words a specific address can be both the shipping and billing address. Other customers can also use that same address if they would like.
Contacts work the same way that the addresses work.
My boss is very weak when it comes to anything SQL. In fact he insisted that my Customer table have two fields that I thought should have been in another table. That is the Customer table now contains SalesRep1 and SalesRep2. I tried to convince him that we would be better off with a CustomerSalesRep table. He's the boss and I have to do with he insists upon.
His concern seems to be with too many tables and too many joins. I don't have enough experience to explain to him why a few joins is no big deal.
Thanks for your help
September 8, 2011 at 10:16 am
My boss is very weak when it comes to anything SQL. In fact he insisted that my Customer table have two fields that I thought should have been in another table. That is the Customer table now contains SalesRep1 and SalesRep2. I tried to convince him that we would be better off with a CustomerSalesRep table. He's the boss and I have to do with he insists upon.
You are absolutely correct on this. What are you going to do when a customer has 3 sales reps. It will happen. There be an "edge" case somewhere and you are going to spend two days pulling your hair out because of poor design. Sounds like Craig was spot on that he is clueless to sql and thinks joins are complicated. You need to normalize your data (which it looks like you did a good job of). His nonsense of adding columns to hold relational data is asstacular. Your boss probably does not understand normalization and why it is a good thing.
Consider your sales rep 1 and 2 scenario. Let's take a step further and use the sales rep name in these columns. Now what is going to happen when your sales rep changes her name? Oh crap, it is now the key used in all those other tables like invoicing and such. Wow suddenly you can't change names because the data is not normalized. This is pretty basic example but I have actually seen systems where names can't be changed because of poor database design.
Dig into some documentation on normalization and the benefits and hopefully you can convince your boss (who imho seems like a micromanager) that it is best for the system and besides, he doesn't have to write the queries so why does he care if you have join several tables?
_______________________________________________________________
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/
September 8, 2011 at 12:29 pm
meichner (9/8/2011)
Thank goodness you don't have to shoot me.
Hehehe!
Let me explain the relationships a little better.
A Customer can have many Shipping/Billing addresses and many Shipping/Billing contacts.
Not a problem with CustID as an attribute.
An Address can belong to more then one Customer or Customer type. In other words a specific address can be both the shipping and billing address. Other customers can also use that same address if they would like.
Not a problem. This is a case however where I *would* repeat data. Bear with me a second. Let's say I have four companies sharing a building, and the only difference between them is the suite numbers. The billing and shipping addresses are the same for all four as well.
I would include 8 entries of the same address (with the different suite #s), for the types. Why? So that I could change one without affecting anyone else and without having to dive through hoops to separate the data and double check multiple usage. Even though you're repeating the data, it's still atomic and specific to a purpose. You will hate yourself if you do it any other way eventually. 🙂
Contacts work the same way that the addresses work.
Contacts are a little different, they are atomic entities that apparently can handle multiple customers. In this case, I would still create a Client specific table, which holds things like the phone number, hair gel, favorite bribe gift, etc. Then create a many-to-many table to connect these Contacts to the correct customers.
My boss is very weak when it comes to anything SQL. In fact he insisted that my Customer table have two fields that I thought should have been in another table. That is the Customer table now contains SalesRep1 and SalesRep2.
Oh boy. I had hoped I was joking...
I tried to convince him that we would be better off with a CustomerSalesRep table. He's the boss and I have to do with he insists upon.
Yep, you do. I do recommend you get it in writing and save the email that ordered you to do this so when you're banging your head against the wall in the future trying to work around the poor design and he jumps up and down you can point at it and go "You told me to use this design, against my wishes, remember?"
His concern seems to be with too many tables and too many joins. I don't have enough experience to explain to him why a few joins is no big deal.
Over-normalized data is a real pita for coders to work against, who want to use combined data for their entities. Sounds like if anything, your boss is a front-end developer. Let me guess, he plans to use Entity Framework or some ORM and run dynamic queries against the system, too?
About the only thing you can do is get your boss to start reading up on Normilization (considered one of the fundamentals) for databases and the reasons why we use it. As Sean mentioned one of the examples above, you can tell it's a critical component. Tell him he really needs to learn to use 3rd normal form and Stored Procedures, even if it pisses off his ORM components. (I'd phrase that more nicely, though).
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 8, 2011 at 12:43 pm
There's another option besides getting your boss to start reading up on Normalization:
You could use your design approach (which I consider to be valid btw) and create views to present the data the way your boss wants you to. Add one or two rather simple queries /procs that deal with each concept returning the same data.
Start with presenting the views to your boss. Then add a few scenarios like Sean mentioned. Show your boss the difference to implement each of those changes into the two design concepts (start with "the boss concept"). In other words: let normalization speak for itself. 😉
September 9, 2011 at 7:35 am
LutzM (9/8/2011)
There's another option besides getting your boss to start reading up on Normalization:You could use your design approach (which I consider to be valid btw) and create views to present the data the way your boss wants you to. Add one or two rather simple queries /procs that deal with each concept returning the same data.
Start with presenting the views to your boss. Then add a few scenarios like Sean mentioned. Show your boss the difference to implement each of those changes into the two design concepts (start with "the boss concept"). In other words: let normalization speak for itself. 😉
I agree with this idea....one caveat I would suggest you note, if you haven't already is how you present data back to the business.
Take for instance a report of customers and their contact details..do you provide a new line for each contact detail /per customer....or something more akin to what the manger expects from his "view" of the data.
just my 2 cents 🙂
eg:
;with CUSTCONTACTS (Cust_id,Contact_Type, Contact_Detail)
as (
SELECT 101,'Phone', '0123 456 789' UNION ALL
SELECT 101,'Phone', '0111 555 789' UNION ALL
SELECT 101,'Mobile', '077711 555 666' UNION ALL
SELECT 101,'Mobile', '0777899 444 888' UNION ALL
SELECT 101,'Fax', '0123 456 555' UNION ALL
SELECT 101,'email', '101@abc.com' UNION ALL
SELECT 102,'Phone', '0122 222 789' UNION ALL
SELECT 102,'Phone', '0122 444 789' UNION ALL
SELECT 102,'Mobile', '06668 321 888' UNION ALL
SELECT 103,'Phone', '0188 888 789' UNION ALL
SELECT 104,'Mobile', '0555 77321 88'
)
--- rather than doing SELECT * FROM CUSTCONTACTS where you get multiple lines foreach record
SELECT Cust_id,
Stuff((SELECT ' ' + Contact_Type + ': ' + Contact_Detail + ' |'
FROM CUSTCONTACTS p2
WHERE p1.Cust_id = p2.Cust_id
--ORDER BY p2.Contact_Type
FOR XML PATH('')), 1, 1, ' ')
FROM CUSTCONTACTS p1
GROUP BY Cust_id
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 9, 2011 at 9:12 am
preciolandia (9/9/2011)
Agreed to everything LutzM said. 🙂
Even though I appreciate the support I reported the post as spam due to the advert list in the signature.
September 9, 2011 at 9:18 am
First I would like to thank everyone for there help.
I was surprised to find that after giving my design some thought, that my boss decided to go with a slightly modified version. For the sake of this discussion, I am not going to include the Customer Contact table because I want to shrink down the scope of my next question.
In my original design I had the following table:
CustomerAddresses
CustID + TypeID + AddressID is the primary Key
I also had (but didn't mention at the time because they were not relevant to what I was asking) two other tables that used the information from the CustomerAddresses table. They are:
Sales Orders
SalesID is the primary Key
ShippingAddressID which is used to point to the Address Table
Invoices
InvoiceID is the primary Key
BillingAddressID which is used to point to the Address Table
My plan was to use the CustomerAddresses table to provide a list of Customers, Types and Addresses to the user so he/she could select a Shipping Address to apply to the Sales Order or a Billing Address to apply to an invoice.
I was going to set the ShippingAddressID on the Sales Order to the Address ID that the user selected. I was also going to set the BillingAddressID on the Invoice to the Address ID that the user selected.
This seemed to make sense to me. It didn't to my boss. He wants me to do the following:
Change the CustomerAddress table in the following manner:
CustomerAddresses
CustomerAddressID as the Primary Key
CustID + TypeID + AddressID as a unique constraint
He then wants me to use the CustomerAddressID from CustomerAddresses as the Foreign Key into both Sales Orders and Invoices instead of using the AddressID from the address table. In his method both the Sales Order and the Invoice point to the CustomerAddress table instead of the Address table in order to obtain the Shipping and Billing Addresses.
Is his method of indirectly obtaining the address by going to the CustomerAddress table rather then going directly to the Address table a good idea or a bad idea? He wants to do this because:
1. He decided that this is the correct way of doing things and
2. If a customer moved from one location to another that instead of changing the AddressID on the SalesOrders and Invoices that he would only have to change the Address ID on the CustomerAddress record.
I don't have enough experience to determine if he is correct or incorrect and I don't have enough experience to know the downside to what he has proposed.
Your continued help is much appreciated.
Thanks
September 9, 2011 at 9:53 am
Here's a scenario:
customer has billing and shipping address A for shipment #1.
Shipment #2 goes to billing addr A and shipping addr. B.
Shipment #3 goes to billing addr C and shipping addr. C.
With your concept it's possible to track any single shipment and return the correct result.
With the concept your boss would like to go, he'll either have to add another row with CustID + TypeID + newAddressID (and keep the old row for integrity reasons) or it would require a history table. In neither of the two scenarios an update would be a valid solution since it would modify previous rows displaying wrong results (e.g. shipment #1 would then show addr. C for billing and shipping, which is simply wrong).
The concept to use depends on the business model, I'd say.
If it's possible that a customer can pay for stuff that will be shipped to a different customer, then the shipping address is not really related to a customer.
On the other side, it makes sense to assign a billing address to a specific customer to avoid sending the invoice to the wrong address. But then it still would need to be decided how to deal with more than one billing address (rsp. shipping address).
I'd probably go with the approach your boss mentioned with the exception that i wouldn't allow any update but rather add "valid_from" and "valid_to" columns.
September 11, 2011 at 9:48 am
LutzM (9/9/2011)
I'd probably go with the approach your boss mentioned with the exception that i wouldn't allow any update but rather add "valid_from" and "valid_to" columns.
I have never used valid to and valid from columns. I think I know what your getting at, but could you
1. Explain there use in more detail?
2. What tables you would put them on?
Thanks so much
September 12, 2011 at 3:46 am
Don't you love it when a boss does not know anything about something but they insist in using the authoritative leadership style.
Look at the bright side, at least it appears that you have dodged the bullet.:hehe:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 12, 2011 at 9:33 am
Welsh Corgi (9/12/2011)
@meichner,Don't you love it when a boss does not know anything about something but they insist in using the authoritative leadership style.
Look at the bright side, at least it appears that you have dodged the bullet.:hehe:
Still not sure that I dodged the bullet yet since I am waiting for an answer to the questions I recently posted.
That being said, I think I am still going down in flames. My bosses method of obtaining the Address using his method of indirection (obtaining the address via the Customer Address table) has one major plus in his mind. If the Address ID was on the Sales Order instead of the Customer Address ID (as I designed it), that we would have to change the Address ID on every Sales Order when a Customer moves. Using his idea the only thing that would need to change is the Address ID on the Customer Address Table.
While his method does make address changes easier, I pointed out to him that it has one side effect. That is all Sales Orders even ones that have already shipped would have their addresses changed. In order to get around this problem he wants me to store the complete address on a Sales Order that was shipped instead of using the Address ID. As of now, I am stuck for a better method to protect historical data if I am going to use my bosses methodology. I thought this would be simple, but apparently its not.
Thanks
September 12, 2011 at 10:25 am
I meant that you were not going to get shot. :hehe:
I feel for you for you are in a tough situation. Been there done that.
Regards
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 12, 2011 at 11:19 am
Welsh Corgi (9/12/2011)
@meichner,I meant that you were not going to get shot. :hehe:
I feel for you for you are in a tough situation. Been there done that.
Regards
Can I take it from what you stated that the best solution (best being a relative term) is my original design where the Orders have a field called ShippingAddress that is a Foreign Key into the Address table, not the Customer Address table?
I am hoping that the more support I get, the better chances that I will be allowed to do this properly.
Thanks
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply