best practices

[dreamdelerium]

hey everyone. i was wondering if anyone can help me out. ill be starting a larger project in a few months and was wondering if anyone had any good articles i could read. im looking for:

1. something about database design. this project could concievable be used on a very large scale. i want to make sure that the way ive been doing it is right.

2. creating user friendly/intuitive design. im creating software for a polulation of people who have very little experience with computers and software

3. planning out software. i find that alot of time is spent going back and correcting issues that were not accounted for earlier on in the planning stages. i might also be working with other people. measure 6 times, cut once philosophy

thanks
jason

[Pradeep1210]

hey everyone. i was wondering if anyone can help me out. ill be starting a larger project in a few months and was wondering if anyone had any good articles i could read. im looking for:

1. something about database design. this project could concievable be used on a very large scale. i want to make sure that the way ive been doing it is right.

2. creating user friendly/intuitive design. im creating software for a polulation of people who have very little experience with computers and software

3. planning out software. i find that alot of time is spent going back and correcting issues that were not accounted for earlier on in the planning stages. i might also be working with other people. measure 6 times, cut once philosophy

thanks
jason

Have a look at the sticky threads in these fourms. They contain very good tips. Some of these tips are even unknown to very good and experienced programmers.

Pradeep

[si_the_geek]

Yep, the sticky thread Database Development FAQs/Tutorials contains lots of articles that would be useful for you, particularly the "Database Design" section, and the articles "What is an Index ..." and "Why should I use Parameters ...".

[leinad31]

More often than not, developers/managers think that they simply have to identify entities, their relationships, normalize the tables, decide on unique/foreign keys implement them at the database as tables and its a done deal... For very small projects this may be true, for larger projects this is NOT the case.

You have to consider the business process, user roles/groups, security, transactions (as this affects table locking), the volume of the data (storage, processed, queried, etc), how often the data changes (as this affects index design and disk I/O), data retention/audit/purging policies, surrogate vs natural keys, serialization of controlled/audited numbers, etc, etc, etc.

Consider a design using natural key (insurance policy number) that is audited and serialized (first in first out, no gaps in series, maintained in another table) and used as foreign key by parent and several child tables. Since its the foreign key (must exist in parent table so records can be created in child tables) is has to be generated at the start at the transaction by retrieval from reference table that holds next available policy number. Due to serialization of policy numbers, reference table will have to be locked (to prevent duplicate policy numbers) until current transaction commits (next policy number updated) or rolls back. This table locking has serious performance implications (bottleneck). Using surrogate key for parent-child relationship and maintaining natural key only at parent table for reference purposes as well as changing transaction processing (serialized policy number saved in parent generated at end of transaction) will alleviate bottleneck.

That was just one aspect... disk I/O, optimal use of database cache, referential integrity implemented at database instead of mid-tier, and still more have to be considered... So you see, its not just about laying out entity relationship diagrams... you really have to understand the business process itself, identify most used/critical sections, and assess how individual transactions will fare when run concurrently in your tentative design.

Database, mid-tier, and front-end should be decoupled from each other as much as possible. Worry about making the interface layout pretty later.