one big table or 15 small tables ?

neelesht · Posted: Tue May 10, 2011 9:45 pm

All,

I would like all your input on what is better, creating 1 single table or creating 15 small tables , below are the requirements :

Data is in this format

A 1 .1
A 2 .4
A 3 .5
B 1 .72
B 3 .21
B 4 .17
C
C
C
AND SO ON (until say O)

No options are , creating 1 single table in the above format and fetching data in 1 query for A,B,C..........O
Another option is creating small separate tables
Table 1 for A
Table 1 for B
Table 1 for C
Table 1 for D
and so on

Which is the better option ?
Total number of rows in 1 single table may be somewhere around 5000 (20 variables, like A,B,C.....). This table may get updated once in a month, new rows may get added, new variables (like A,B,C) may get inserted.

What would be better option , to create small tables or to create a big table.

Let me know if you have any queries.
Thanks

daveporcelan · Posted: Tue May 10, 2011 10:06 pm

Are you talking a mainframe DB2 table here?

If so, one table with 75000 rows is tiny.

We have tables with 20+ million rows on a regular basis.

Multiple tables would be a huge waste of time and resources.

Use ONE table. This is not even close.

neelesht · Posted: Tue May 10, 2011 10:51 pm

Yes, I am talking about Mainframe DB2 tables.

Ok Dave taken, and I agree on that.

But wont 15-20 small tables make things easier, simple, simple select queries ?

Logically all same set of data should be in 1 table instead of having different sets in 1 table.

I know maintenance of 1 would be simpler then maintenance of 20 but I wanted to weigh the options on every scale.

daveporcelan · Posted: Tue May 10, 2011 11:00 pm

neelesht · Posted: Tue May 10, 2011 11:08 pm

Exactly thats what my point is, data is not logically the same ................and that why I thought separate tables would make sense

e.g
Table 1
state az 1
state al 2
state ca 8
state ma 9

Table 2 (no of units has nothing to do with state)

No of Units 1 .7
No of Units 2 .23
No of Units 3 .21
No of Units 4 .21
No of Units 5 .18

Here these 2 are separate entities no logical relation between them, then why not represent them in a separate table.

I am not arguing, but just trying to understand and trying to enhance my knowledge.

Thanks

Phrzby Phil · Posted: Tue May 10, 2011 11:15 pm

Clean logical organization is very important in any system design.

Sounds like you want separate tables.

daveporcelan · Posted: Tue May 10, 2011 11:17 pm

You have done a very poor job of describing your data.

Were we supposed to understand that:

neelesht · Posted: Tue May 10, 2011 11:33 pm

Ok in my first post A and B are State and no of units in my last post.
In my first post I just wanted to give an example by not going to the actual values.

""Now you are talking two tables not fifteen: State_Table Unit_table.""

This is what I am trying to bring up, creating 1 table will help me improve performance.
Creating more tables may affect performance but logically it will make sense.

state az .17
state al .18
state ca .19
state ma .21
no of units 1 .42
no of units 2 .41
no of units 3 .43
no of units 4 .44
month of accident jan .52
month of accident feb .51
month of accident mar .50
month of accident apr .49

Hope this makes more sense. Also, I am more inclined towards separate tables but need good and solid reason .

dbzTHEdinosauer · Posted: Tue May 10, 2011 11:40 pm

What you need to do is sit down and read about db2 design.

before designing a db, you need to understand the data.

you have only provided us with limited information,
from which you want a justification for your actions.

does not work like that.

you want a professional answer,
stop wasting our time with rookie hypothetical situations.

you want help, take the time to explain your data,
the source of the data,
how it is used with other parts of your system,
what are your users' needs?

daveporcelan · Posted: Tue May 10, 2011 11:42 pm

What is the decimal unit (second column) in your example?
Are they the same for all the types?

Is there any relationship between state, units, and month?

If not then they can not be on the same table.

But you may want one table for all the states. One for all the units, and one for all the months.

Just asking, have you ever designed a DB2 application?

neelesht · Posted: Wed May 11, 2011 12:01 am

Thanks for the reply. I can not provide the actual data here, not even the columns.

Wanted just to understand what should be the criterias when you go for 1 table and when you go for separate tables.

Anyways dont bother wasting your precious time,I will discuss with other people.

As always Thanks.

""Just asking, have you ever designed a DB2 application?""
I will ignore this. :-)

daveporcelan · Posted: Wed May 11, 2011 12:09 am

don.leahy · Posted: Wed May 11, 2011 12:17 am

I have seen this type of design before. It was used to store small code tables used by a large application.

The table was defined with generic column names:

neelesht · Posted: Wed May 11, 2011 12:27 am

Dave, no I was not at all sarcastic and trust me the responses I got will surely help me alot in designing.

I am Sorry if I sounded sarcastic.

Don, am still going through your response, will get back to you all soon.

Thanks alot everybody.

GuyC · Posted: Wed May 11, 2011 1:21 pm

Don is right: it's a fairly common design for code/decode, allowed-values table. ("common" is not equal to "standard")
Advantages :
- creating a new code-attribute is easy: no new table,....
- single maintenance/report program, housekeeping => using static SQL.
Disadvantages :
- No Referential Integrity, but possible to resolve with constraints/triggers.

the other approach is a table for each code.
- single maintenance/report program will have to be dynamic SQL.