Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
keen1
Starting Member
21 Posts |
 Posted - 2004-10-05 : 04:54:09
|
| We are considering a database design case for one of our plants which runs 24x7 and we aim for high flexibility in terms of production data storage; let me explain a little.We have a main transaction table which stores all the produced materials; a material goes through several processes so we will have one record for a material in each process. We need to store changing number of process variables. So we will have an auxillary table which will store the process data corresponding to that specific material. All the process data has its own id defined.One alternative design would be to create seperate tables for each of the process and link the material id from the main table to each of these auxillary tables. But then we will need to change our business layer to accompany these changes ( either adding another process variable or deleting one of them ).Second alternative is great for flexibility. There will be a single process data table with 4 columns. One is an identity column, second one is the material id from the main table ( foreign key ), third one is the process variable id and the fourth one ise a nvarchar column for the value of that variable. With this design, we will never need to change our business logic; user interface layer can retrieve the value of any process data for a specific material.We aim for the second alternative but we are not sure about the performance with millions of data after several years. Does anyone had a similar design study or have a suggestion? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-05 : 05:51:48
|
| >> But then we will need to change our business layer to accompany these changes ( either adding another process variable or deleting one of them ).unless you implement an abstraction layer so that you can change the database structure.What makes you think separate tables will give better performance?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
keen1
Starting Member
21 Posts |
Posted - 2004-10-05 : 06:21:03
|
| Seperate tables approach would make the index size smaller; that was the reasoning. Do you think it is not correct?>> But then we will need to change our business layer to accompany these changes ( either adding another process variable or deleting one of them ).>>unless you implement an abstraction layer so that you can change the database structure.i have never seen an implementation of that kind of approach; we need to talk to our partners about that. Can you say something about that approach? |
|
|
|
|
|
|
high flexibility versus performance