Creating Sturdy Databases in SQL
With SQL, getting the structure of a database right is pretty important if you want to be able to pull accurate information from it later. Surprisingly however, many novice database developers give little consideration to how the various parts of a database relate to each other.
The key concept for building and maintaining a reliable database is data integrity. There are a whole host of ways that a database can go bad, but happily, many of these can be avoided by maintaining data integrity. A relational database is a hierarchical structure, which means that integrity must be maintained on several levels.
Each table in a database represents an entity that is uniquely identifiable. The fact that it has been given entity status means that it is important in its own right, independent of how it may relate to other parts of the database. A table must be internally consistent, structured in such a way that modification anomalies are not possible, as well as correct in its relationships with any other tables to which it is connected by a primary key to foreign key link. By maintaining entity integrity, domain integrity, and referential integrity, you can assure that the databases you create will be reliable repositories of your organization's data.