One large table or a few smaller ones?

[Dembrey]

Hi,
I've question about design and efficientcy I hope someone can shed some light on.

I have a database that tracks various types of reports (anomaly reports, change notice reports etc.), each report has some specific fields so has its own dedicated table. Each report type can have multiple attachments.

So currently I have a separate attachments table for each report type. Each of these tables has the same structure ie (these aren't the real field names btw).
[Autonumber key]
[Report_id] <= foreign key of 'parent' report
[Attachment number] <= attachment numbering per report
[Attachment description]
[File Path]

Now I need to list all attachments regardless of their parent report. I know could do this with a UNION query but I was wondering:

Would it be more efficient to group all my attachments into one table and then have separate queries when I need to deal with attachements of a particular report type (ADVANTAGE: one table =easier managment, DISADVANTAGE: slower performance) or go with the separate tables/UNION approach (ADVANTAGE: better performance?, DISADVANTAGE: mutiple tables more complex?)

The DB structure is a FE/BE with the BE on the network drive and local FE's on each users PC.

If anyone is still with me after such a long question, any thoughts?

Cheers.

 

[WayneRyan]

Dembrey,

I'm surprised that nobody has responded to this yet.

I would definitely vote for the single table approach here. You have a
clear-cut way of retrieving your data, you are not "double-storing" things,
and it is the most straightforward way of storing it.

Wayne

 

[Dembrey]

Wayne,

Thanks for the reply.

I'm erring on the side of your argument. The worry I had was performance, since I understand that Access always 'retrieves' the entire table content from the BE before performing any query on it. But the easier management side of things prehaps out weighs this.

Cheers.

 

[KenHigg]

Dembrey,

How many records do you think you could eventually have?

kh

 

[Dembrey]

Ken,

I would guess in the thousands (rather than tens of thousands).

I think I'm being over sensitive about this performance (network clogging) issue so I'll go with the one table approach.

Many thanks.

 

[KenHigg]

Sounds like a good choice to me.

kh