| Database Design Question - Looking For Suggestions [message #21772] |
Wed, 28 August 2002 19:28  |
Rizwan Qazi
Messages: 135
Registered: August 2002
|
Senior Member |
|
|
This is a rather generic question so please don't shoot me for it :-
I have a database where each month I create a new table to input the
monthly data. For e.g
CUSTOMER_200101
is created in January
CUSTOMER_200102
is created in February
.... and so on
In the previous solution the table XYZ has to be altered by adding a
new column for each month data added.
Any suggestions for a new solution where there is a table CUSTOMER where
I can insert rows instead of column and a table XYZ where does not
need to have columns added to accomodate the extra data.
Does this need further explanation?
Regards,
Riz Qazi
|
|
|
|
| Re: Database Design Question - Looking For Suggestions [message #21773 is a reply to message #21772] |
Wed, 28 August 2002 19:57  |
Venky
Messages: 52
Registered: October 2001
|
Member |
|
|
Hi,
In the same table itself have one column period_yearno
- datatype is number and it takes the value YYYYMM. ie 200201 . When u maintain data in this fashion, u can also do the order by the year and then the month. There is no need of creating a new table or new column for each month. You can insert rows using database triggers or procedures.
Venky.
|
|
|
|
| Re: Database Design Question - Looking For Suggestions [message #21785 is a reply to message #21772] |
Thu, 29 August 2002 13:29 |
Rizwan Qazi
Messages: 135
Registered: August 2002
|
Senior Member |
|
|
Hi Venky & Everyone Else,
That part I understand and am pretty clear on - however in curent solution specific values are added from new table and added to the existing table as XYZ
for e.g.
-EMPLOYEE_199906- with fields...
CONTRACT_NO
BALANCE
CREDIT_LIMIT
-EMPLOYEE_199907- with fields...
CONTRACT_NO
BALANCE
CREDIT_LIMIT
-EMPLOYEE_199908 - with fields...
CONTRACT_NO
BALANCE
CREDIT_LIMIT
Are Currently stored in table XYZ as
CONTRACT_NO
BALANCE_199906
CREDIT_LIMIT_199906
BALANCE_199907
CREDIT_LIMIT_199907
BALANCE_199908
CREDIT_LIMIT_199908
If a new table
-EMPLOYEE_199909- with fields...
CONTRACT_NO
BALANCE
CREDIT_LIMIT
is added the table XYZ is altered to look like
XYZ as
CONTRACT_NO
BALANCE_199906
CREDIT_LIMIT_199906
BALANCE_199907
CREDIT_LIMIT_199907
BALANCE_199908
CREDIT_LIMIT_199908
BALANCE_199909
CREDIT_LIMIT_199909
This table is used for reporting purposes. Can you or anyone think of a more "elegant" solution for this?
Thank You
Riz
|
|
|
|
|
|
| Re: Database Design Question - Looking For Suggestions [message #21832 is a reply to message #21772] |
Tue, 03 September 2002 13:36 |
Rizwan Qazi
Messages: 135
Registered: August 2002
|
Senior Member |
|
|
Mike, Venky's solution was to add an extra column instead of bringing in a new table all the time. The second part of my question was
Whenever a new table is added, a column is
added to an already existing table to
accomodate certain values from the new table.
Adding columns periodically to existing tables
is untidy - I was looking for a more elegant
solution
For example for table employee whenever a new month data is loaded a new column has to be added to CUSTOMER_AGGREGATE. Do you have any suggestions.
Thanks
Rizwan
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
