Design table for meet the requirement is not so simple. Sometimes rule and process of business change rapidly. We must predict the future before we start to create the table. The table structure we made could impact anywhere which related it. As you could see the logic for application , reports, calculation, batch, etc depend on the design of the table. Sometimes we think the table is unpowerfull or too much field unrequired...
One example to handle this requirement for current and future development :
The requirement is : every product have own rate accumulated per year, and not every year is at the same rate but we assumed that the maximum rate until 100 years or less. So the simulation is like this :
--bad design table
--take too much columns
create table mst_rate_product
(
code_product nvarchar(20) not null,
rate_year01 decimal(10,4),
rate_year02 decimal(10,4),
rate_year03 decimal(10,4),
rate_year04 decimal(10,4),
rate_year05 decimal(10,4),
rate_year06 decimal(10,4),
rate_year07 decimal(10,4),
rate_year08 decimal(10,4),
rate_year09 decimal(10,4),
rate_year10 decimal(10,4),
--...
rate_year100 decimal(10,4)
primary key (code_product)
)
--recommended design table structured
--powerfull on data requirements and saving data
create table mst_rate_product_recommended
(
code_product nvarchar(20) not null,
year int,
rate decimal(10,4),
primary key (code_product)
)
We don't have to affraid to make a complicated report by logic because sql server 2005 have pivot table to make a report based on column(if needed) not by rows.
Regards,
Wihemdra
