Hi all,

I’m trying to do an optimal database design and I’m stuck. In a nutshell what I have and what I’m looking for: 

• There is a multi language (4 countries) application.
• For each language I need to store 1 500 000 rows per year.
• The table has only 4 columns and one FK.
• There are 4 views selecting from the countries: employee, sum of top 10 salaries, count of work shift in a month.
• Insert into DB is 1 a day, selecting from the view is 1000 a day.

The question:
Shall I store data in one big table with 4 500 000 rows per year and make the views with language condition (Sample 1) or separate data into 4 small tables (Sample 2)?

Sample 1:
----------------------------------------

T_Results
IdResult
IdImport
Employee
Salary

T_Import
IdImport
ImportDate
Language

Sample 2:
---------------------------------------- 

T_Results_EN
IdResult
IdImport
Employee
Salary

T_Import_EN
IdImport
ImportDate
Language

T_Results_DE
IdResult
IdImport
Employee
Salary

T_Import_DE
IdImport
ImportDate
Language

Thanks a lot!
Marw

I think this is hard to call, especially without doing any load and performance testing. However, upfront I'd go with the single table solution and an associated  language table. I would then establish a baseline and continue to monitor the performance, and if need to be, consider a horizontal partitioning of the rows, potentially based when it was inserted or similar.

 I agree with the previous poster - to me the problem sounds like a single table partitioned by language.

It would be a good option to split this table up into smaller tables.  That way there's less data to search through so your performance will increase.

Just make sure you have your clustered and non clustered indexes as without them, you'll have a heap and CRUD work will be painful.

I disagree with splitting into smaller tables, but I certainly agree that a clustered index should be added, if not already, to the tables, preferably the ID columns, and non-clustered indexes for any field that is searchable, because of the number of rows.

Hi all,
thanks for all the useful opinions. I have decided to store the data in a big table. If I’ll see the performance of the Views is not optimal I’ll try to use 4 tables instead the Views and update them once a day.

Thanks for help.

Marw