one large table or many small ones?

[Guest]

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...

 

[Jeff Boyce]

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>

 

[tina]

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