Approaches to building an efficient database_问答_开发者

I was wondering if I could draw on experienced database designers. I;m not very experienced with building databases and I have a project to complete within set time (couple of months). The requirement is to build it to third normal form.

I was just wondering what the best way to start would be? Should I go ahead and build something that works (trying to be as efficient as possible) as then go back and refacto开发者_高级运维r any parts that can be improved or is there some methodology to follow to ensure certain degrees of normalisation from the start?

Do not build a database with the intent to go back later and normalize it. Databases do not refactor as easily as application code for one thing and for another, you already have a requirement to follow the third normal form, so do so from the start, it will be far less work and in the end a far better product. There is not shortcut to correct database design in the third normal form. Just get to understand clearly what it is before starting to deisgn.

If you are unfamiliar with database dsign in the third normal form, go spend a couple of days doing some reading on database design. THen spend a day or so reading about performance tuning a database - designing a database from the start to perform well will save you a lot of time. It doesn't take longer than designing a poorly performing datbase, once you know what to do and what to avoid. So my first advice to you is to get a couple of big fat books and read in depth before you start. The time you spend doing this will be well repaid in your ability to do things better through the rest of the project.

You will certainly write many layers (DAL, BAL, UI) on top of this database layer. Every change at a lower level will bubble upwards and cause more re-work than just at the database layer.

I think you should do all your design and get the database to the best normalization required/needed to save your data without anomalies at the outset.

Unlike Front-Side programming, refactoring a database strucure, once in use, is not only painful, but impractical.

I strongly recommend getting the structure of the back-end right up front. THe good news is that third normal form is not generally difficult to acheive. In fact, once you get a little practive, it becomes almost second nature to think of data in those terms.

The Third normal form essentially states that each component of a single record in a table be unique to the key for that record. COnsider:

tblPerson: PersonID PK LastName FirstName MiddleName DOB SSN IncomeSource IncomeAmount

The Table as defined above is NOT in thrid normal form, since income source is not unique to each incidence of Person in the table, and in fact is subject to change over the life of that person. Instead, one would structure the above (in overly simplistic terms) as follows:

tblPerson PersonID PK LastName FirstName MiddleName DOB SSN

tblPersonIncome PersonIncomeID PK PersonID FK tblPerson IncomeSourceID FK tblIncomeSource IncomeAmount AsOfDate

tblIncomeSource IncomeSourceID IncomeSource

Note that some will argue about my use of auto-incrementing ID fields above - While these do not violate the 3rd normal form, they also do not utilize a natural key.

If your back-end is properly normalized from jump, adding or extending your entities is much easier to do in the furure, and will pose a much smaller impact upon any front-end you create prior to the additions.

Anyway, design correctly out front on your back end, or the misery will be intense once you get rolling and need to implement structural changes later. To do otherwise would be a little like pouring the foundation for your new house, and planning to change the floorplan to suit your needs later, while building it.