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.
  

• insert into user ("1", "owen");

  is a bad insert always specify the column names.
  
• 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

  select firstname + ' ' + lastname as 'fullname'  

  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.
  
• Alphabetic sequence table aliases are confusing.

  select a.*, b.*, d.name, c.*, d.* from lions a, monkeys b, users c, group d where a.id = 1; 

  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.

Transformers

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

<input name='first_name' >

. 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.

Over Normalisation

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.

permanent link. Find similar posts in Articles.