One Table or Multiple

[Tommy408]

I have records that can be stored over multiple tables or all in one table. I was wondering if it would be faster to query over multiple tables or in one table.

If it is over MULTIPLE tables, my application would know which table to query for it's data ( 100 records per table). If it is in ONE table, then I wonder if it would be slower to look for its data when going through thousands of records. I'm not sure how Jet does its thing.

Thank you.

 

[fneily]

Normalization answers that question. Access is a RELATIONAL dbms. Did you normalize your tables before you asked that question? Did you go through at least the first three normal forms (steps) of the standard normalization model? If not, it won't matter how you split up your data.

 

[lespaul]

below is a link to a document that will explain what it is and the basics of how to do it

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Tommy408]

There's no data duplication. Every spaces will be use. There are over 10 computers accessing this table concurrently. First 100th records for computer 1, second 100th records for computer 2...etc. Since computer 1 query know where the data for computer 1 is stored, it can get it's 100 records within thousands of records.

So I was just wondering if it's more efficient for each computers to have it's own table. I won't be looking at these data, it just need spaces to stay.

 

[Tommy408]

My SQL statement is very simple, for example it would be like
WHERE COL1 = 'SMITH'

If I were to store all the computers data in one table:

SELECT COL1
FROM MYTABLE
WHERE COL1 = 'COMPUTER1_SMITH';

If I were to store all the computers data over multiple tables:

SELECT COL1
FROM COMPUTER1_TABLE
WHERE COL1 = 'SMITH';

 

[lespaul]

What do you mean:

First 100th records for computer1....?

I'm still not sure what you are trying to do and why.....

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Tommy408]

I don't think I can explain better than that.

I'm going with multiple tables as all the client computers have different query statements and it doesn't need to search other data that doesn't belong to a specific client computer.

I just hate all the tables that will be created and clogging up since I won't be looking at them.

Thank you for your help.

 

[PHV]

it doesn't need to search other data that doesn't belong to a specific client computer
So, why the same database for all those computers ?
SELECT COL1
FROM MYTABLE
WHERE COL1 = 'COMPUTER1_SMITH';
This query is meaningless as it returns only one column with same value for all rows ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tommy408]

That should be *

If I were to store all the computers data in one table:

SELECT *
FROM MYTABLE
WHERE COL1 = 'COMPUTER1_SMITH';

If I were to store all the computers data over multiple tables:

SELECT *
FROM COMPUTER1_TABLE
WHERE COL1 = 'SMITH';

PHV, you're right it doesn't have to be on the same database. But my employees work with these data through forms, so wouldn't it be faster if the data is on the same MDB file along with the forms instead of updating data with DAO?