September 20, 2008 at 5:34 pm
I am in the process of developing an ERP which should have the capability of handling bulk data.
My database is ready but i am bit confused that whether my database architecture is perfect or not on following grounds :
1. I am having lengthy tables of up to 50 columns but all these 50 columns will not contain any redundant values so is it a good idea to keep single table for this or shd we keep it in multiple tables ?
2. I am using transactions and stored procedures for saving , updating and deleting so if i will save the data in one table will it affect the performance of the system later on during the stage or not ?
3. How i will ensure that database integrity is being maintained along with the fast accessibility.
4. How to make sure that database changes are very rare so that development time line doesn't gets affected.
Please let me know if you need more details about the database and project for better clarification i will be highly obliged if i can get good suggestions for improvements in the database.
Thanks
September 21, 2008 at 3:08 am
shishirbabel (9/20/2008)
1. I am having lengthy tables of up to 50 columns but all these 50 columns will not contain any redundant values so is it a good idea to keep single table for this or shd we keep it in multiple tables ?
Are all the columns applicable to all the rows? Do you have repeating groups (Phone1, Phone2, Phone3)? Is the table sufficiently normalised?
2. I am using transactions and stored procedures for saving , updating and deleting so if i will save the data in one table will it affect the performance of the system later on during the stage or not ?
Not sure I understand you fully.
Data modifications always happen within the context of a transaction. If you don't define one explicitly, then SQL defines on implicitly and auto-commits once the statement has completed successfully.
If you use explicit transactions, make sure that you have good error handling (I recommend try-catch on SQL 2005) and appropriate logic to detect errors and rollback as required.
Also make sure that you check parameters and the like before starting the transaction to see if you can catch errors before they happen.
3. How i will ensure that database integrity is being maintained along with the fast accessibility.
Proper normalised design. Useful indexes. Well written, set-based code. Make sure that you have the appropriate primary and foreign keys, that you have check constraints where necessary, that columns that can't be null are defined as such and that you have unique constraints where applicable.
4. How to make sure that database changes are very rare so that development time line doesn't gets affected.
Changes to data or changes to structure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2008 at 2:39 am
hi,
We have worked for several ERP's systems. if you could share structure, we might really have good suggestions. Otherwise its very difficult to understand and to comment on.
September 22, 2008 at 3:36 am
Please find the attached database.
Please suggest about the structure and what you think can be modified ?
September 22, 2008 at 6:29 am
From the looks of the diagram, that database has very few primary keys and no foreign keys. How are you planning on enforcing data integrity?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2008 at 8:20 am
I couldn't see any relationships between the tables if its there.
Also there should be few primary keys to the tables and foreign keys to relate tables.
why don't you send us the entire script or db backup
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply