IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

one big table or 15 small tables ?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
neelesht

New User


Joined: 24 Jul 2006
Posts: 99
Location: Los Angeles

PostPosted: Tue May 10, 2011 9:45 pm
Reply with quote

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
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Tue May 10, 2011 10:06 pm
Reply with quote

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.
Back to top
View user's profile Send private message
neelesht

New User


Joined: 24 Jul 2006
Posts: 99
Location: Los Angeles

PostPosted: Tue May 10, 2011 10:51 pm
Reply with quote

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.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Tue May 10, 2011 11:00 pm
Reply with quote

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


No, it is the opposite. Based on the value you are looking for, you would need seperate selects for each table. Just changing the where clause on a single query is much easier.

The data is logically the same, then one table is the way to go. Period.
Back to top
View user's profile Send private message
neelesht

New User


Joined: 24 Jul 2006
Posts: 99
Location: Los Angeles

PostPosted: Tue May 10, 2011 11:08 pm
Reply with quote

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
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1050
Location: Richmond, Virginia

PostPosted: Tue May 10, 2011 11:15 pm
Reply with quote

Clean logical organization is very important in any system design.

Sounds like you want separate tables.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Tue May 10, 2011 11:17 pm
Reply with quote

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

Were we supposed to understand that:
Code:

A 1 .1
A 2 .4
A 3 .5
B 1 .72
B 3 .21
B 4 .17


Was: State Code - No of Units - Some other value ???

If this data has nothing to do with each other then why are they on the same records?

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

You have to do a better job of describing your requirements if you want any help at all (or designing a solution for that matter).
Back to top
View user's profile Send private message
neelesht

New User


Joined: 24 Jul 2006
Posts: 99
Location: Los Angeles

PostPosted: Tue May 10, 2011 11:33 pm
Reply with quote

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 .
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue May 10, 2011 11:40 pm
Reply with quote

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?
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Tue May 10, 2011 11:42 pm
Reply with quote

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?
Back to top
View user's profile Send private message
neelesht

New User


Joined: 24 Jul 2006
Posts: 99
Location: Los Angeles

PostPosted: Wed May 11, 2011 12:01 am
Reply with quote

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. :-)
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania

PostPosted: Wed May 11, 2011 12:09 am
Reply with quote

Quote:
Just asking, have you ever designed a DB2 application


Do not ignore this.

I was not asking to be rude. Not at all.

The question was a very fair one based on the level of knowledge you had shown with your questions and examples.

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



Was this meant as sarcasm. I was was trying to be helpful, then you turn it around with a snide remark. Not very professional.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Wed May 11, 2011 12:17 am
Reply with quote

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:
Code:

CREATE TABLE GNRC_1
TBL_ID   CHAR(04)
,NUM_1  SMALLINT
,ALPH_1 CHAR(10)
,NUM_2  DECIMAL(5,2)
etc


Views were used to distinguished between the various tables. For example, a view designed for a Province/State lookup (TBL_ID = 'PRST') would be defined as:
Code:

CREATE VIEW TBL_PROV_STTE
(PROV_STTE_CD)
AS (SELECT ALPH_1
      FROM GRNRC_1
      WHERE TBL_ID = 'PRST')


Relationally speaking, there are lots of things wrong with this approach.

But, if used judiciously, there are some practical advantages, such as not having to create a new table whenever a new type of code is introduced; all you have to do is create a new View on the existing generic table. It also makes it fairly easy to create a single maintenance screen that can be used to update many different code tables.

In the case I saw, performance wasn't an issue because the table was properly indexed and only had a few thousand rows in it.
Back to top
View user's profile Send private message
neelesht

New User


Joined: 24 Jul 2006
Posts: 99
Location: Los Angeles

PostPosted: Wed May 11, 2011 12:27 am
Reply with quote

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.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed May 11, 2011 1:21 pm
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts small int to zoned decimal conversion DFSORT/ICETOOL 3
No new posts Unload and Load ISPF Table TSO/ISPF 4
No new posts ISPF Table to add a new column TSO/ISPF 1
No new posts Inserting into table while open selec... DB2 1
No new posts Load new table with Old unload - DB2 DB2 6
Search our Forums:

Back to Top