Setting Up An Efficient Database

[JoeF]

I am trying to set up a database system at work and I want to arrange the database in the most effiecent way. I don't want to set up a megatable, but I think the most efficient way to set up the database is to set up one of my sets of data into a fairly large table.

I work in a direct mail business, so there are basically two keys for my data. One key is "sku number," which is a unique number assigned to each product. The other key is "mailing id," which is a unique number assigned to each mailed package. For each sku, there are certain fields that remain consistent, no matter the package delivered; this should be one table. For each mailing id, there are certain fields that remain consistent, regardless of the product; this should be another table.

My problem comes when setting up the table/tables for mailing id - sku, in which fields will be kept that are specific for a product in a particular mailing package. The problem is that in this set of data, there are approximately 20 fields (detailing sales, returns, customer payment info, shipping costs, profit margin, special pricing, etc.). Is it more efficient to enter all this information into one table, which would be extremely large? Or would it be more efficient to categorize the fields into several smaller tables, even though the fields "sku" and "mail id" will be repeated in each table?

 

[dneufarth]

Sounds very much like Order Entry. Try creating a database using the Order Entry model under File->New Database->Databases and playing with it.

Check out the tables, relationships, forms in design mode for underlying details. The Customer, Orders, Order Details tables seem particularly applicable to your challenge.



Dave