September 12, 2011 at 11:34 am
meichner (9/12/2011)
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
That's how I've done it. FK to addresses, not to customers, when it comes to shipping data. Makes more sense, causes less headaches in the long run.
I'll third the motion to normalize the data correctly, then stack views on top of it to present it in a denormalized form for those needs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 12, 2011 at 12:53 pm
Hi,
I would agree with Evil Kraig F when he states that you don't want the contacts or addresses belonging to multiple customers. Also when he states that you should duplicate the addresses rather than sharing them between customers.
I've been working with Purchase Order systems for ten years and when I started I thought like you did, however over the years the hard lessons I learnt are:
1) Customers data should ALWAYS be separated from other customers.
2) Any detail that could change must be retained so that all prior orders, invoices, payments show exactly the way they did at the time of order.
With PO systems I would say that these rules are not just important, they are sacrosanct.
If you imagine you and your room-mate both order from example.com, if example.com used one record for both of your addresses, what happens when your roommate moves out and suddenly your orders are going to his address?
If a customer changes address then goes to view an historic invoice, would it be correct to show the new address or the old address?
Customers who experience issues with small things like addresses not being consistent will loose confidence and start to question your ability to store more important things like their bank details
When you consider these rules your six tables becomes four (probably five as I too dislike your types code table)
Customer
CustId is the Primary Key
Address
AddressId is the Primary Key
FK'd to CustId
FK'd to AddressTypeId
Contact
ContactID is the Primary Key
FK'd to CustId
FK'd to ContactTypeId
AddressTypes
AddressTypeId is the Primary Key
ContactTypes
ContactTypeId is the Primary Key
This way two customers who ship to the same address have no chance of the addresses being changed accidently due to the other customer moving, etc.
When explaining this to my SQL illiterate boss, we ended up referring to it as cross-contamination.
We have to slightly bend the rules with normalisation when dealing with PO systems.
Some data will be duplicated as the validity of the order is more important than the duplication it may cause. However you can consider the data to be simply a "State" and then you can normalise allowing for the different states of the data.
Take for example an order for a book, the book cost £20 when you purchased it, if the price falls to £10 you should still see on your invoice that it cost £20, not £10.
So what you can't have is a simple table that lists products that joins to an orderContents table.
You either need to store in the orderContents table all the details of the item as it was at the time of purchase, or you have to (effectively) disallow updates on the products table, only allowing a new row to be inserted that is then the active row, whilst disabling the old row, so that historic orders look at the row that was active at the time of order and new orders only use the current active row.
We favoured the storing all details in the orderContents table as people rarely look at old invoices, but customers want to get to the products list all the time so fewer rows in the products table the better.
So for your addresses table you have two choices, either allow updates but store the address on each order, or use the active inactive approach and just store the addressId.
So I would say that in your post, if you are not storing the address on the order, then you are correct that you should be linking to the address, but that the address detail should not be changeable. Yes, you should be able to change an address on an order before it's shipped, but that's just changing the link to another row in the addresses table.
If you store the actual address in the order then this problem goes away, but that is too much duplication for my liking. Products change all the time, addresses don't so much so the active/inactive works much better for addresses than for products.
Ultimately, what's the difference between going from one address to two addresses then back to one address or just changing from one address to another address? Nothing, they still end up with one active address and one inactive address so why not store two address rows?
Hopefully I’ve not misunderstood anything, and that I’ve helped rather than hindered.
SgtWilko
September 12, 2011 at 1:17 pm
There's one concern with de-normalizing the structure for your boss in regards to views, which I didn't see mentioned (sorry if someone brought this up).
We need to determine why the denormalization needs to happen for the front end. If it's for data-display only, all's good. If it's to be the data-interface, you're going to have a few concerns.
A view CAN be updated through, according to certain rules.
http://msdn.microsoft.com/en-us/library/ms180800.aspx
In particular, you can only update one of the tables at a time. This becomes important when data is presented in a non 1 to 1 relationship, or what 'appears' to be a single record in the schema is not.
Before you end up using views as the great answer to all your problems in your boss's eyes, you may want to review their limitations first, so that the next time he decides it's the way to proceed, even if it works for this case, you can be sure it is.
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 13, 2011 at 1:23 pm
CELKO (9/13/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. <<It is pretty obvious that you have not had a data modeling course. The skeleton code you posted is almost useless; we need to see the DRI and other constraints to help you.
Let's begin with ISO-11179 naming rules. Your tables are singular,m which means that they have only one row. Oops! Make them plural or collective nouns for the entities. There is no such nightmare as a “type_id”; those are what we call attribute properties which means that you can have a “<something>_type” or a “<something>_id”; this is sreiahgt from freshman logic and the Law of Identity – To be is to be something in particular; to be nothing in particular or anything in general is to be nothing at all. This is wrongly attributed to Aristotle.
>> The main table [sic: not RDBMS] 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...) <<
The Main or Master file/tape was a term used in network databases. In RDBMS, we have referenced and referencing tables. Let's do this one thing at a time:
CREATE TABLE Customers
(cust_duns CHAR(10) NOT NULL PRIMARY KEY,
..);
What industry standard are you using for the customers? I like DUNS, but we have no idea what industry you are in. This is a set of entities.
CREATE TABLE Addresses
(san CHAR(10) NOT NULL PRIMARY KEY,
..);
Does your industry use a Standard Address Number (SAN)? Or do you use the FedEx, UPS, etc systems? This is a set of entities.
CREATE TABLE Contacts
(contact_duns CHAR(10) NOT NULL PRIMARY KEY,
..);
I am going to assume that a contact is a person or company. It could be a relationship in the form of an event from the vague narrative you posted. “Customer John Smith screamed at us on 2011-09-16” would be a contact.
Now it get tricky because we have relationships.
CREATE TABLE Customer_Addresses
(cust_duns CHAR(10) NOT NULL
REFERENCES Customers (cust_duns)
ON DELETE CASCADE
ON UPDATE CASCADE,
address_type CHAR(3) NOT NULL
CHECK (address_type IN (..))
san CHAR(10) NOT NULL
REFERENCES Addresses (san)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (cust_duns, address_type, san),
UNIQUE (??);
The list of address types is short and constant; why put it in a table? Now, can a customer hve more than one address of a particular type? Add UNIQUE (cust_san, address_type)
At this point, I am lost from lack of DDL and specs. Here is a guess that this is log of events (they sued us, they twittered us, whatever)
CREATE TABLE Customer_Contacts
(cust_duns CHAR(10) NOT NULL
REFERENCES Customers (cust_duns)
ON DELETE CASCADE
ON UPDATE CASCADE,
contact_duns CHAR(10) NOT NULL
REFERENCES Contacts(contact_duns)
ON DELETE CASCADE
ON UPDATE CASCADE,
contact_event_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
contact_event_type CHAR(5) NOT NULL
CHECK (contact_event_type IN (..)),
PRIMARY KEY (cust_duns, contact_duns, contact_event_date)
);
I apologize for not following any naming conventions. I am also planning on reading your stairway series on design (I just came across it) in order to improve my design skills as well as help me provide more details when I post questions.
Based on what I posted and the fact that most posters understood what I asked, I do believe I got my point across. I do understand your point that more being more specific leads to more clarity. I thought a surface explanation was sufficent.
There was one portion of my question that you didn't reference in your response. That portion is the key to my question.
That being said, I am going to try to simply things with the following senerio:
My company has a set of customers. These Customers buy products from my company. Each Customer has a specific set of locations where they want those products shipped to. A Location can be used by more then one Customer. On Occassion Customers move. When a Customer orders products from my company we create a Sales Order that specifies where to ship the product.
Given that information, I would appreciate it if you could give me your opinion as to what tables and keys I would need in order to support this senerio. The key problem I am having is what table/key the Sales Order needs in order to refer to the proper shipping address.
Thanks
ps. My application does not use DUNS or SANS or any of those standards. The reason for this is that we have to interface with a legacy system (written in 1980) that has its own way of doing things. I am therefore restricted in what I can and cannot do.
September 13, 2011 at 1:39 pm
sgtwilko (9/12/2011)
Hi,I would agree with Evil Kraig F when he states that you don't want the contacts or addresses belonging to multiple customers. Also when he states that you should duplicate the addresses rather than sharing them between customers.
I've been working with Purchase Order systems for ten years and when I started I thought like you did, however over the years the hard lessons I learnt are:
1) Customers data should ALWAYS be separated from other customers.
2) Any detail that could change must be retained so that all prior orders, invoices, payments show exactly the way they did at the time of order.
With PO systems I would say that these rules are not just important, they are sacrosanct.
If you imagine you and your room-mate both order from example.com, if example.com used one record for both of your addresses, what happens when your roommate moves out and suddenly your orders are going to his address?
If a customer changes address then goes to view an historic invoice, would it be correct to show the new address or the old address?
Customers who experience issues with small things like addresses not being consistent will loose confidence and start to question your ability to store more important things like their bank details
When you consider these rules your six tables becomes four (probably five as I too dislike your types code table)
Customer
CustId is the Primary Key
Address
AddressId is the Primary Key
FK'd to CustId
FK'd to AddressTypeId
Contact
ContactID is the Primary Key
FK'd to CustId
FK'd to ContactTypeId
AddressTypes
AddressTypeId is the Primary Key
ContactTypes
ContactTypeId is the Primary Key
This way two customers who ship to the same address have no chance of the addresses being changed accidently due to the other customer moving, etc.
When explaining this to my SQL illiterate boss, we ended up referring to it as cross-contamination.
We have to slightly bend the rules with normalisation when dealing with PO systems.
Some data will be duplicated as the validity of the order is more important than the duplication it may cause. However you can consider the data to be simply a "State" and then you can normalise allowing for the different states of the data.
Take for example an order for a book, the book cost £20 when you purchased it, if the price falls to £10 you should still see on your invoice that it cost £20, not £10.
So what you can't have is a simple table that lists products that joins to an orderContents table.
You either need to store in the orderContents table all the details of the item as it was at the time of purchase, or you have to (effectively) disallow updates on the products table, only allowing a new row to be inserted that is then the active row, whilst disabling the old row, so that historic orders look at the row that was active at the time of order and new orders only use the current active row.
We favoured the storing all details in the orderContents table as people rarely look at old invoices, but customers want to get to the products list all the time so fewer rows in the products table the better.
So for your addresses table you have two choices, either allow updates but store the address on each order, or use the active inactive approach and just store the addressId.
So I would say that in your post, if you are not storing the address on the order, then you are correct that you should be linking to the address, but that the address detail should not be changeable. Yes, you should be able to change an address on an order before it's shipped, but that's just changing the link to another row in the addresses table.
If you store the actual address in the order then this problem goes away, but that is too much duplication for my liking. Products change all the time, addresses don't so much so the active/inactive works much better for addresses than for products.
Ultimately, what's the difference between going from one address to two addresses then back to one address or just changing from one address to another address? Nothing, they still end up with one active address and one inactive address so why not store two address rows?
Hopefully I’ve not misunderstood anything, and that I’ve helped rather than hindered.
SgtWilko
I just want to say that you, Evil Kraig F and Mr. Lutzm have given me a lot to think about.
Thanks so much.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply