Multiple Tables or one big table

[Daniel Lees]

Hi

i am developing a database within which i am storing information about
projects. Each project has information for every month of the project. I was
wondering whether it is better to have one big table with the job number then
month or whether it would be better to have a table for each job which only
its months in. there are bout 200 projects so that would mean 200 tables or
roughly 6000 records in one table. It is easier to design using seperate
tables as i will just copy each table into a temp when the user wants to view
it this way there is no way any other project information can be seen while
looking at a diff project. But i was wondering from a speed point of view
whether there would be any difference. There will be up to 10 ppl using the
database across a network. The temp table woudl be local to stop interference
so that isn't an issue, i am looking at purely speed point of view and
possibly size.

Thanks in advance for any replies

cheers

danny

 

[David F Cox]

So the CEO is down on project X99 with the project leader of Y33and they
want to look at project Y33 and all you have to do is copy it for them as
soon as you come back from being off sick.

or

Project leader Y33 signs in with his password.

I have some info on another PC, l'll get a copy of it and back to you. :->

 

[Daniel Lees]

Hi

I don't know what ur on about. Basically i want to know if it is better to
have one big table for all job numbers or have a seperate table for each job
number with a temp table to view the info.

Cheers

Danny

 

[Douglas J. Steele]

6000 rows is a pretty tiny table for Access. If you had, say, 600,000 rows
then I might be concerned (although I know of applications with millions of
rows)

Having only one table is definitely the better approach.

 

[Jerry Whittle]

One table. What happens when someone wants a report that compares and
contrasts the various projects. Access can't handle a Union query of 200
tables and filling a temp table from 200 other tables would take a lot of
time plus bloat the database.

BTW: 6000 records is a drop in the bucket for Access.

Actually now that I think about it, the textbook answer to your question is
neither. Your data should be properly normalized and that should drive the
number of tables. Certainly putting the same kind of data in numerous tables
is wrong; however, properly normalized I bet that you have more than one
table.

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

 

[David F Cox]

I apologise for my response. IMO the right way to do this is to have one
large table. You should only allow Access to the database via a "Front-end",
which will consist of forms and reports. The users should have to sign in
with a password to each project, which should prevent unauthorised users
from viewing or editing projects that they do not have rights to.

A big table will have very little performance degradation.Records are mostly
accessed via an index, and via links, and these are very efficient. There
should be very little plowing through all of the data to get just the
project that you want. The data will always be up to date. The concept of
copying a database when the user wants to see it horrified me, which was why
you got the response you did. The user should be be able to see, and much
less, edit, data in table view. There will be only one file to back-up and
maintain.

Good luck.