written by owen on 2004-Nov-24.
As before this is article is intended for ASP and PHP web developers, it will not teach you how to make a database. I will not try to convey database design methologies or formulate why one type of database is better than another. I am merely stating observations. Design your database - keep it simple, keep in mind that will probably need to expand it in the future. Bad, lazy mistakes in the initial design may spell doom for everything.
One with the Data
The database is not a place you simply store information. Often times the database is the program itself - you may not see it now but eventually you will realise that a program or web site is only as good as it's database. Respect the data, it is more important than the output displayed to the user. Having a hundred tables is not a good thing - optimize.
Web site or rocket ship
Creating databases for a website is different than creating a database for a c++ program or desktop application. Keep your information light, fast, dynamic and easy to find.
Auto increment Ids
Put auto increment ids on every table in your database - do it. Yes I said every table. Usually this column should ALWAYS be named "id".
Table and Column Naming Conventions
It is important to maintain a constant naming convention for the columns in your tables. For example if you have a date column called "modified_date" in one table try to use the same in all other tables. There are a few things you should do and a lot of thing you should not;
- Do not put the name of the table in any of your columns - ever. Everybody knows the the table is groups, so having the first column being called groups_id is really !smart.
select groups.group_id from groups where groups.group_name = 'silly'
- Never add a "s" or make plural the name of your table. The "user" table is used to store "users". Above I mentioned a table called "groups", you will soon learn why that is an exception to the rule.
- Do give your columns good names such as first_name and last_name. fname and lname are not good alternatives.
- Do use lowercase database, table and column names. A table called ManagersRequest will only get you into trouble. Novel?
Write good queries
The quality of the queries you write is directly dependent on how your database is designed. The term good query is of course relative to your application but I will outline a few ways to help you write good queries;
- Queries that join more than 5 tables are bad website queries. Something is wrong in the design somewhere.
is a bad insert always specify the column names.
insert into user ("1", "owen");
- If it takes more than a minute to execute then something is definately wrong.
- If you have to run queries in a loop based on the results of another query then you are crazy. Because you have a 5ghz machine does not give you the right to absorb all of the resources on a bad query, don't be evil.
- There are quite a few ways to solve a problem. Sometimes smart code is necessary and a smart query is impossible.
- Use the query engine and the database functions to you advantage. It is more efficient to add
in the query than it is to do it in the code. Use date functions and database features if you are allowed and whenever possible.
select firstname + ' ' + lastname as 'fullname'
- Alphabetic sequence table aliases are confusing.
You will notice that you continually have to look at the "FROM" cluase to know which table relates to which alias. Use the first letter of each word in the table name as the alias for the table.
select a.*, b.*, d.name, c.*, d.* from lions a, monkeys b, users c, group d where a.id = 1;
Avoid having multiple identites of your database objects. If you have a field called first_name in a table and you create a form name the
. Do not have hundreds of naming variations e.g. fname, fn, FirstName, Firstn, fn, f_n. Such a situation creates room for confusion and confusion leads to errors and errors lead to very bad things. Keep it consistant all the way through. You are not writing a novel.
<input name='first_name' >
Normalisation may have been taught to you at some point and it for the most part is a good idea. You may never have heard of the term "Over Normalisation". There is a very very thin line between normalisation and the point where you go crazy. I have seen it happen - look out for it.