View previous topic :: View next topic
|
Author |
Message |
neelesht
New User
Joined: 24 Jul 2006 Posts: 99 Location: Los Angeles
|
|
|
|
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 |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
|
|
|
|
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 |
|
|
neelesht
New User
Joined: 24 Jul 2006 Posts: 99 Location: Los Angeles
|
|
|
|
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 |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
|
|
|
|
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 |
|
|
neelesht
New User
Joined: 24 Jul 2006 Posts: 99 Location: Los Angeles
|
|
|
|
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 |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006 Posts: 1050 Location: Richmond, Virginia
|
|
|
|
Clean logical organization is very important in any system design.
Sounds like you want separate tables. |
|
Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
|
|
|
|
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 |
|
|
neelesht
New User
Joined: 24 Jul 2006 Posts: 99 Location: Los Angeles
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
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 |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
|
|
|
|
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 |
|
|
neelesht
New User
Joined: 24 Jul 2006 Posts: 99 Location: Los Angeles
|
|
|
|
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 |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006 Posts: 792 Location: Pennsylvania
|
|
|
|
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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
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 |
|
|
neelesht
New User
Joined: 24 Jul 2006 Posts: 99 Location: Los Angeles
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
|