New Database Design Thoughts : Help ?
-
Hi,
I have these process around these data types of tables plans and actuals
PLANS : Forecast table
PLANS : Sales Plan table
PLANS : Production plan table
ACTUALS : Sales table
ACTUALS : Production table
ACTUALS : Stock table
ACTUALS GROUPS : Customer name table, Sales District table, material category table, plant name table.
Each PLANS table has a process via a manual user data input process (from Client MS Access data input), the ACTUAL tables will be updated by DTS (bcp) process. So each table (plan and actual) requires stored procedures around update, delete, insert and views processes. All tables have normalisation grouping with 'ACTUAL GROUPS' tables.
Question : How would set up the database(s) ?
1) A Database for each table type
2) A database for the PLANS types and a separate database for ACTUALS table types.
3) ALL in one database.
How does database set up effect performance with cross database ownership(linking).
I feel that one database for each table and its processes will be best as it means all SP's will only related to that table. Then pull data together to do reports in a REPORTS database ? What structure would you recommend and why ?
-
I don't see any need for seperate databases.
One of the main reasons for not putting information in different databases would be data integrity. You can't define foreign key constraints for data in seperate databases.
Another often overlooked reason is ongoing maintenance. Seven or eight seperate databases can mean you have as many different maintenance and backup plans.
You also eliminate the need for your REPORTS database and the extra processing required for loading the data there as well.
When approaching the sort of design decision, I've always found the best advice is KISS (keep it simple stupid)
If you have a final schema, post it here with details about why things are the way they are. I'm sure there would be an ample number of people that can advise on the rights and wrongs of certain design decisions you've made
--------------------
Colt 45 - the original point and click interface
-
..."One of the main reasons for not putting information in different databases would be data integrity. You can't define foreign key constraints for data in seperate databases."..
Here I would like tables from one database to another.There could be a 100 scripts in one database, thats a lot to manage ? But your points are valid.
Do you know of any code generator for doing Triggers based on database relationships ?
-
I don't understand your requirements for having different databases. Maybe you should explain your overall process a bit more.
In SQL Server having 100 scripts is by no means a lot. Some database applications can have 1000's of stored procedures. We have one database that acts purely as an offline datastore for reporting. It only has 63 tables, but it has 164 stored procedures for inserting, updating and deleting records.
If you're thinking of using triggers from the start, then you're heading down the wrong track. They can be a real pain to maintain and cause huge performance bottlenecks.
--------------------
Colt 45 - the original point and click interface -
Your correct about the triggers, I will try and avoid them, and to do more data checking to the data before it is added to the table.
ie Data upload in datainput table, datacheck data, then if all ok add them to the main storage table.
I am dealing with uploads of 1000 to 4000 records at a time.
Thanks for your help.
Could you make more comments why triggers can be bad ?
-
In terms of SQL Server capacity I don't see any problems with the number of records and database objects. It may seem a lot when you look at it from an Access point of view, but looking from the other end, it's really very small.
EG: For the offline datastore (ODS) that I mentioned in my previous post, we have two databases. One is a staging database, the other is for the ODS itself. Every night we clear out the data from the staging database and load approx. 20,000,000 rows of data. Then a series of stored procedures are run to synchronise the data in the staging database with the ODS. Currently the ODS is about 15gb and the staging database is usually about 3gb. In total we have over 40,000,000 rows in the ODS. Even given these large numbers there are other users that have that many rows in one table.
If the database is designed with appropriate constraints in place, there should no, or very minimal need to use triggers in day-to-day processing. Probably the only time you should use triggers is when you have strict auditing requirements. Even then there are better third-party alternatives available.
--------------------
Colt 45 - the original point and click interface
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply