View Single Post
  #5 (permalink)  
Old November 25th, 2011, 10:56 AM
Rod Stephens's Avatar
Rod Stephens Rod Stephens is offline
Wrox Author
Points: 3,166, Level: 23
Points: 3,166, Level: 23 Points: 3,166, Level: 23 Points: 3,166, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jan 2006
Location: , , .
Posts: 647
Thanks: 2
Thanked 96 Times in 95 Posts

1. You could store that information in all three tables. The main problem occurs if, for example, an employee is also an employee or a vendor is also a customer. In those cases you'd store someone's information in two places. That can lead to inconsistent updates where you update the person in one place but not in another.

Depending on your business, that might not be a big problem, but the database would have the possibility of containing inconsistent data.

The reason the database was designed that way in this example is that the Customer, Employee, and Vendor classes inherit from Person. (See Figure 12-8.) That leads naturally to the design with a separate Persons table.

I've seen databases that keep this kind of data separate and they generally work okay. They tend to not worry about possible inconsistencies so they eventually may contain employees with different addresses than the same person in the Customers table, but no one particularly cares. It just means that some junk mail sent to the customer doesn't get delivered, but that happens with normal customers, too, who move and don't tell the retailer.

2. In this case, I would make the Persons table's PersonId field be a foreign key constraint for the Customers table so you could not create a Customers record until the Persons record was already created.

So you would first look up the customer in the Persons table to make sure he or she isn't already there (in case the customer is also an employee or vendor). (Alternatively you could try to insert the data and see if you violate a uniqueness constraint.) If the person isn't already there, add it. Then create the Customers record.

> Are CustomerID and PersonID auto numbers?

That's what I would do. [Digression: But some companies use an algorithm to generate these numbers to hide other data such as the date or customer initials. Or they may add some sort of checksum. For example, credit card numbers have a specific format that identifies the kind of card. The number tells you whether a credit card is a Visa, Mastercard, or Amex based on the number) and whether it may be valid (they must satisfy some checksum formula). Technically that information is redundant and should be stored elsewhere, but realistically you won't be updating that information in your database (Visa will never change your credit card number so it looks like a Mastercard number) so the information is really for use external to your database.]

> Why don't we just use CustomerID and link it to the [Persons] table.

You can't use CustomerId in the Persons table because Customers, Employees, and Vendors records might have the same ID in those tables. You need to generate a unique PersonId for the Persons table and then you can use it in the other tables.

So you would create the Persons record and that would auto-generate the PersonId. Then you would use the PersonId to create the Customers record (for example).

Let me know if that is unclear or if you have other questions. (Please start a new thread for unrelated questions so it's easier for others to see what they are about by looking at the titles.)

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)