Ed, In that case (and I assume your web server and database server are on the same cluster), your original idea is good: store foreign keys in your invoice table pointing to all other tables. In other words, once an order is entered, there is also an entry to the invoice table.
However, you should use a stored procedure or some kind of application to sort out the result for you. A table should only deal with hard data, not any soft behavior. Just in case if in the future you need have the invoice viewed repeatedly , then it's better to save the sorted result as files on your web server, or blob data in a separate table. Partitioning your invoice table based on time periods would also be an idea. -----Original Message----- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Thursday, February 26, 2004 12:16 PM To: 'Zhao, Charles'; [EMAIL PROTECTED] Subject: RE: Design Advice? Hi Charles, Answers: 1. Frequent web viewers 2. Medium... internal website 3. An invoice will get frequent views while it's active, but very few views after it's completed. 4. About 1200 entries a month. Are there approaches other than what I described? -Ed -----Original Message----- Your business requirement is not quite clear. Here are some questions that you need to answer before you finalize your design: 1. How often will the invoice be viewed (and I assume the viewers must be your web site users?) 2. What's the approximate traffic volume? 3. Will each time the users view different invoices (invoices of different periods), or will they often review the same invoices? 4. How big are your order tables? If it's too much work on your database side, you may need to do some work in your application. -----Original Message----- Hi =) How would you approach the design of a database that models the following information? - Users - Invoices - Purchase Orders - Sales Orders - Adjustments - Products We were originally working with Users, Purchase Orders, Sales Orders, and Products. Everything was pretty easy at first... Individual tables were created for each item and web pages were created to add, list, view, edit, and delete table records. For example, click Add User to add a user. Click List Users to list users. Click on a specific user to view their specific information, etc.. Purchase orders have their own information, but they also have individual line items. Since the number of line items varies per PO and to keep things simple, I created a separate table for PO line items. When you go to display a PO, it displays information from the PO table and then displays relevant line items. That part was all pretty easy... and then we added the idea of invoices. Invoices have their own information... easy enough, create an invoices table... but... the catch is that invoices need to list all purchase orders, sales orders, and adjustments for a given time period. I could display the invoice and then display purchase orders and then display sales orders, etc. But that's not going to work. I need the invoice to display all transactions sorted by date. Does MySQL allow selecting data sets from multiple tables into a single data set that can be sorted and then displayed? My understanding... I'd have to create a separate table to do this. I'd also have to keep track of entry types so the user can click a line on the invoice and get more detail. Mind you, I know I could do a bunch of PHP programming to accomplish this, but it would be cumbersome. That makes me wonder if I'm using the correct approach to designing the database. One idea is to create a new table that would serve to cross reference the Invoice table with all orders. It would have the ID field, IvoiceID, EntryTypeID, EntryID. Then I'd just sort this new table and branch according to EntryTypeID. Is that the best approach though? In case my above description is confusing, it might be easier to think of this as a check register where checks, deposits, and adjustments are stored into individual tables because each item has unique information. In order to display the register properly, do I end up needing a "register table" that then links to the other tables? Anyway, I hope I'm making sense. Any thoughts, ideas, or comments on how you'd approach this are greatly appreciated. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
