1 or many Tables? (1 Viewer)

[Sam Summers]

Hi there

I am using 17 tables to hold information on equipment that each use the same fields.
My reason is that for any one piece of equipment there could be as many as 500 - 1000 items so if I were to use one table it may have to hold up to 17000 items.
Is the way I am currently doing it better than having one potentially large table and for Access to have to search for all the items of the required name during searches/queries?

Thank you in advance

Sam

 

[Pat Hartman]

The answer is an unqualified NO. You need only two tables. One to define the equipment and a second to define the items that are related to a particular piece of equipment.

tblEquipment:
EquipmentID (primary key)
EquipmentDesc
etc.

tblItems:
EquipmentID (primary key field1)
ItemID (primary key field2)
ItemDesc
etc.

You might actually need three tables. You didn't tell us enough about your app to determine this. But if Equipment has a many-to-many relationship with Items, you'll need a third table.

tblEquipment:
EquipmentID (primary key)
EquipmentDesc
etc.

tblItems:
ItemID (primary key)
ItemDesc
etc.

tblEquipmentItems:
EquipmentID (primary key field1)
ItemID (primary key field2)
etc.

Using 17 tables will cause nothing but headaches when creating form/reports/queries. You'll either have to create separate forms and reports or use a single version whose recordsource will need to be changed in code for each different piece of equipment. Plus, if you add a new piece of equipment, you'll need to add a new table and its associated objects.

Do some reading on database design and normalization if you don't understand my suggestions.

 

[Sam Summers]

Thanks very much Pat,

I had set this up already but it was good to get confirmation that I was doing the right thing.
Basically the normalisation tells the story and Like you said with multiple tables, - queries etc. begin to get awkward.
It never pays to jump ahead without normalising properly.

Thanks

Sam