If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
one large table or many small ones?
My data consists of many records and a lot of fields for each record. My
initial thought was to define an ID for each record and create multiple tables where the fields are grouped by topic. But I guess I could also just create a single table with many fields. Is there any reason to choose one over the other? I think breaking it down would make it cleaner, but I would have to store the ID about 10 extra times. Here is a semi-example, it should not make sense, but hopefully you can see how the fields can be grouped, but don't need to be: ID: 1 Distributor: SFS Cost: 76.55 Arrival Date: Jan 2, 2003 Number of Bananas: 18 Number of Apples: 83 Number of Oranges: 12 Number of Pineapples: 7 Number of Fruits from Florida: 56 Number of Fruits from California: 14 Number of Fruits from Washington: 50 So, for the above data, should I create a single table with every field or would it be better to create 3 tables, one with ID, Distributor, Arrival Date, and Cost; another with number of bananas, apples, oranges, pinapples and ID; and another with number of fruits from Florida, California, Washington and ID? Thanks. -Tom... |
#2
|
|||
|
|||
Step away from the computer!
What you outlined is a very reasonable design ... if you were using a spreadsheet! If you want to use a relational database (e.g., Access), you'll need to wrap your head around normalization and relational design. That is, if you want to take advantage of the functions and features that Access offers. If these aren't important to you, maybe a spreadsheet would be a better idea after all. Good luck Jeff Boyce Access MVP "topgunnin8" wrote in message ... My data consists of many records and a lot of fields for each record. My initial thought was to define an ID for each record and create multiple tables where the fields are grouped by topic. But I guess I could also just create a single table with many fields. Is there any reason to choose one over the other? I think breaking it down would make it cleaner, but I would have to store the ID about 10 extra times. Here is a semi-example, it should not make sense, but hopefully you can see how the fields can be grouped, but don't need to be: ID: 1 Distributor: SFS Cost: 76.55 Arrival Date: Jan 2, 2003 Number of Bananas: 18 Number of Apples: 83 Number of Oranges: 12 Number of Pineapples: 7 Number of Fruits from Florida: 56 Number of Fruits from California: 14 Number of Fruits from Washington: 50 So, for the above data, should I create a single table with every field or would it be better to create 3 tables, one with ID, Distributor, Arrival Date, and Cost; another with number of bananas, apples, oranges, pinapples and ID; and another with number of fruits from Florida, California, Washington and ID? Thanks. -Tom... |
#3
|
|||
|
|||
looks like you're tracking merchandise (fruit) received from various
distributors. based on that analysis, suggest the following tables as a starting point: tblDistributors DistID (primary key) DistName (any other fields that describe a distributor) tblMerchandise MerchID (primary key) DistID (foreign key from tblDistributors) ArrivalDate (any other fields that describe a received shipment of merchandise - invoice# perhaps, etc) tblMerchandiseDetails MerchDetailID (primary key) MerchID (foreign key from tblMerchandise) Item Origin ItemCount Price so your example record, placed into the above tables, would create 1 record in tblDistributors (this table is a list of all your distributors) 1 record in tblMerchandise 4 records in tblMerchandiseDetails i'm assuming you'll have a unit price or a total price available for each item, so that data should be stored in the Details table - and the total price for the shipment should *not* be stored in a table, but calculated whenever needed in a query, form, or report. likewise, the number of fruits from s specific point of origin (FL, CA, WA, etc) is not stored, but calculated as needed by grouping the Details records on the Origin field. strongly recommend that you read up on data normalization and table relationships. failing to do so is the most common, and most expensive, mistake that "newbies" make. you can find links to tons of info at http://www.ltcomputerdesigns.com/JCReferences.html hth "topgunnin8" wrote in message ... My data consists of many records and a lot of fields for each record. My initial thought was to define an ID for each record and create multiple tables where the fields are grouped by topic. But I guess I could also just create a single table with many fields. Is there any reason to choose one over the other? I think breaking it down would make it cleaner, but I would have to store the ID about 10 extra times. Here is a semi-example, it should not make sense, but hopefully you can see how the fields can be grouped, but don't need to be: ID: 1 Distributor: SFS Cost: 76.55 Arrival Date: Jan 2, 2003 Number of Bananas: 18 Number of Apples: 83 Number of Oranges: 12 Number of Pineapples: 7 Number of Fruits from Florida: 56 Number of Fruits from California: 14 Number of Fruits from Washington: 50 So, for the above data, should I create a single table with every field or would it be better to create 3 tables, one with ID, Distributor, Arrival Date, and Cost; another with number of bananas, apples, oranges, pinapples and ID; and another with number of fruits from Florida, California, Washington and ID? Thanks. -Tom... |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Get data from combo box to popluate the next box | Lin Light | Using Forms | 4 | December 30th, 2004 05:01 PM |
Manual line break spaces on TOC or Table of tables | Eric | Page Layout | 9 | October 29th, 2004 04:42 PM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |