I guess it all depends on the potential size of the database. If it is going to be large and you want to search on it quickly then what tends to happen these days is people start of with a normalized data structure and then de-normalize until they reach a happy medium...aka data warehousing. Even better use WDDX!......you can have the data that you need to search on in seperate fields and chuck the rest of it into a field using WDDX....CF then deciphers it. This may well be overkill for what you are doing but just a thought........ ------------------------------------------------------------------ Andrew Ewings Project Manager Thoughtbubble Ltd ------------------------------------------------------------------ -----Original Message----- From: Bob Silverberg [mailto:[EMAIL PROTECTED]] Sent: 28 September 2000 13:03 To: CF-Talk Subject: RE: Slightly OT -- Database Question It's difficult to build a good data model from the limited amount of information provided, but assuming that is _all_ the information you need to store, the quickest answer (not necessarily the best), would be to store the data in two tables. Order Table ----------- OrderID Quantity CardID OrderInfo Table --------------- OrderID (which is also a FK to the Order Table) ProofRequired CustomerComments ReorderNumber DealerPONumber DealerNote OperatorInitial OperatorNote This would potentially save space, but makes your model more complicated. If you really want to model it properly (i.e., normalized), you should consider whether you are looking at a supertype/subtype situation, in which you might end up with many tables. For example: Order Table ----------- OrderID Quantity CardID OrderInfo Table --------------- OrderID (which is also a FK to the Order Table) ProofRequired CustomerComments ReorderNumber OrderDealer Table ----------------- OrderID (which is also a FK to the Order Table) DealerPONumber DealerNote OrderOperator Table ------------------- OrderID (which is also a FK to the Order Table) OperatorInitial OperatorNote You would do this if there was a logical grouping of optional attributes. For example, if it were often the case that you needed to store DealerPONumber and DealerNote, but _not_ OperatorInitial and OperatorNote. Of course, you also have the option of storing everything in one big table. This would depend on your storage and performance considerations. Hope this is what you were looking for, Bob -----Original Message----- From: Craig A. Zingerline [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 27, 2000 4:12 PM To: CF-Talk Subject: Slightly OT -- Database Question Hello, I have a question regarding database design. I have an application that will take information for orders. This application will have certain required fields and certain optional fields. I am wondering what the best table structure breakdown will work best for this application, as you will see there are many more optional fields than required fields: Required Information: OrderID Quantity CardID Optional fields: RushDelivery ProofRequired CustomerComments ReorderNumber DealerPONumber DealerNote OperatorInitial OperatorNote Thank you for any help/suggestions! Sincerely, Craig ---------------------------------------------------------------------------- -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. ---------------------------------------------------------------------------- -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. ------------------------------------------------------------------------------ Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
