Faster: 100 tables/1k names -OR- 1 table/100k names?

kaanuki · #1 (**permalink**) 06-22-06, 18:18

Hello all.

I am somewhat of a novice when it comes to db designs, so i think it would be prudent of me to ask this before designing my database application.

I am designing a database for a company with 100 departments. Each department will have 1,000 Employees. Taking growth into consideration, Which database structure for the Departments would perform faster, and why?
100 tables/1,000 names each?
-OR-
1 table/100,000 names (using a DEPARTMENT field)?

Thank you in advance.

[I will be using MySQL or MS SQL].

r937 · #2 (**permalink**) 06-22-06, 18:51

if there is any chance, even the slightest chance, that someone will ask you for a report that has to encompass data from all departments, ask yourself what the sql for that query would look like

this should settle your question in a trice

andrewst · #3 (**permalink**) 06-23-06, 05:28

No offence intended to kaanuki, but why do large organisations persist in delegating database design to self-confessed novices? Is their data really so unimportant to them?

Pat Phelan · #4 (**permalink**) 06-23-06, 09:30

Quote:

Originally Posted by andrewst

No offence intended to kaanuki, but why do large organisations persist in delegating database design to self-confessed novices? Is their data really so unimportant to them?

My first guess is that the organization is a college.

-PatP

RedNeckGeek · #5 (**permalink**) 06-23-06, 10:29

or... the novice figures out how to use databases, and starts developing apps. The company learns the value of gathering data, and says "Hey, could you build an app that does this?"

urquel · #6 (**permalink**) 06-23-06, 11:18

This could be considered a security method. If someone wants to steal the data, it will be more difficult to query 100 tables.

r937 · #7 (**permalink**) 06-23-06, 11:24

@urquel: chortle

kaanuki · #8 (**permalink**) 06-25-06, 20:07

Come on guys...

The question is MOSTLY theoretical to provide a clearer picture for you to answer my question about using efficient database designs.

Why do you have to attempt to degrade me by analyzing me/my needs simply to show what experts you are. I already know you guys are smart that is why I came here. But I asked for help in analyzing DATABASE DESIGN, not My database knowledge/usage.

Can someone please provide me with an intelligent answer versus an analysis?

...Again, Which design is more efficient in terms of speed and accessibilty?

kaanuki · #9 (**permalink**) 06-25-06, 20:12

??????????

r937 · #10 (**permalink**) 06-25-06, 20:13

in terms of speed and accessibility, if the tables are properly indexed, they should be the same

in terms of administration, or being able to retrieve summary information from the entire set of data, one of those approaches is insane

hope that was intelligent enough for you

r937 · #11 (**permalink**) 06-25-06, 20:23

Quote:

Originally Posted by kaanuki

ps... r937 SQL Consultant,

SELECT * FROM tblCOMPANY
[WHERE Dept. = '...']

Was that a trick question or something?

no, i was serious, your query will look something like this --

Code:

select sum(foo) as total_foo
  from ( 
       select foo from department001
       union all
       select foo from department002
       union all
       select foo from department003
       union all
       select foo from department004
       union all
       select foo from department005
       ...
       union all
       select foo from department099
       union all
       select foo from department100
       )

urquel · #12 (**permalink**) 06-26-06, 09:34

This is a mistake that is frequently made in design. It is frequently incorrectly assumed that a particular design will not preform well. So, the database is designed around "assumed" performance. It is better to create a design around the nature of your data and business rules. The final physical implementation and queries can generally be tuned for performance. Splitting a table into 100 separate tables can usually be done behind the scenes by the DBMS if needed for performance.

jfabasques · #13 (**permalink**) 07-14-06, 07:45

Normalizing your design will probably not sum up to 100 tables, but I believe its not a single table only. Even a simple design has 4 or more tables but not more likely 100 tables. Learning what DBMS is all about will be a good help.