You are currently viewing the BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
Hello Rod. I have read a few of your books. No doubt your approach of database design is superior from others. They are very practical. However I'm quite disappointed that you do not implement the design of figure 14.4 in a database. You could at least make available a download of the full implementation in Access 2007 so that I could understand its implementation. Make up a couple of fictionous data for all the tables and create some forms and queries.
You see Rod, I've been crawling to arrive at fig 14. 4 and then, oh no! It stops here. The design is quite complex. If you need to write another book, lets say "Beginning Database Design Continued", and show the implementation, then I would look forward to buy it.
For instance I'm lost as to why [Persons] to [Vendors] and [Customers] are 1 to 1 but [Persons] to [Employees] is 1 to many. And how is the [Persons] table functioning to its subclasses. Oh, so much confusion...
The book focuses on design rather than implementation so I didn't build all of the example databases in a particular database. Partly I didn't want to assume that you use a specific database because the details about how you do things like create foreign key constraints is different in different database systems. Chapters 15 and 16 were the exceptions.
For this particular example, I would probably also continue to 3NF and implement the structure shown in Figure 14-5.
As to your question about Employees/Person being many-to-one, I think that must have been a typo. Person is a detail table that provides extra detail about someone. I can't think of a reason why one Person might correspond to multiple Employees. (Unless an employee is schizophrenic.) I suspect that error got into the model in Chapter 13 and then just got carried along into the later versions.
We are looking at the possibility of making a new edition to this book. I'll see what the editor thinks about providing database implementations, although I think we still want to keep the book as database-neutral as possible.
Thanks for the ideas and for pointing out the error in the design.
That 1 to many bug has been bugging me quite a while. Like you said, it crept quite early in the chapters.
Now things are more clearer. Anyway the second edition with more improvements is welcomed because this is such a complex topic. If possible give more examples of the data of the tables so that we can see better what is going on.
Or another suggestion, write a second edition of "Visual Basic .Net Database Programming" and include the "The Pampered Pet" as one of its implementation. You can use .net 4.0 in the new edition where as we know it is much easier than ado to program database. Better still, implement also web based database using GridView, DetailsView / FormView. I really liked your first edition but it is using older technology and less flexible.
Thanks again for the enlightment, and yes, what I mean is 14.5, not 14.4
1. How is the [Persons] table helpful in this design? Can we not just place the fields in their respective [Employees], [Vendors] and [Customers] tables?
2. Lets say we want to create a new Customer. Do we start with the [Customers] table first or the [Persons] table, or start simultaneously? Are CustomerID and PersonID auto numbers? Why don't we just use CustomerID and link it to the [Persons] table.
I know, I'm quite mixed up. Could please help me in this. Thanks.
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.)
Oh yes. Regarding your suggestions for a new edition. They're good suggestions. I do want to try to keep the book as vendor-neutral as possible but it might be helpful to include some concrete examples in specific databases such as Access, SQL Server, or MySQL.
Unfortunately examples like that can take up a lot of space so putting a full example in the printed book might mean leaving other information out. Perhaps I can build some examples to post on the book's web pages.
Using bound controls can be very convenient. Unfortunately because database programming is so important to so many applications, the libraries get changed a lot so every release of Visual Studio, for example, seems to include new classes for manipulating databases. That means any specific example is outdated by the next release. Often you can keep running the program for quite a while but you fall off the technology curve. (Most recently WPF has greatly changed the way you do data binding.)
In many programs I skip the automatic binding and do it myself. I write code in a separate module to fetch the data and display it in ListBoxes, ComboBoxes, TextBoxes, or whatever other controls are useful. It only takes a little more work but I have more complete control over when, where and how the data is displayed. (For example, binding a ComboBox to multiple database tables is hard.)
Most importantly when there's a new release, my code still works. Even if the underlying database operations change (and they change much less often than the controls and binding methods), I only need to update my database module and I'm good to go again.
Anyway, I may try to put some more concrete examples and some data binding examples in the next edition. Or they may be available for download. I'll have to see what the publisher thinks would be the best use of pages for readers in general (and not just Visual Studio users).
Keep those suggestion coming! They're very helpful.
I think that would work in this example, although it doesn't quite feel "right" somehow.
In this example I'm assuming that all customers, vendors, and employees must have Persons records. If you don't require that, then you couldn't use one ID for everything because you might, for example, want to create a Customer without a Person.
But if you assume that all three must have Persons records, then I thikn it would work.
(I think the difference is mainly philosophical. In the design with separate IDs you're thinking about Customers, Vendors, and Employees and the Persons table provides extra detail for them. If you use the PersonId everywhere, then you're thinking about Persons and the Customers, Vendors, and Employees tables provide additional detail for them.)