[Vince]

MySQL Table designs and foreign keys

Introduction
So apparently many people want to use MySQL, but they have no idea on how to properly design a table structure. MySQL is an RDB. Relational Database. With the emphasis on that first word. Make relations! Too many people try to make a large gamemode with one, or maybe two tables. No!

Note: This tutorial assumes that you are familiar with database terminology. Words like database, record, field, table, key, etc .. should not appear foreign to you.
Note 2: Your database must be running on the InnoDB engine. Foreign keys do not work with MyISAM.

More rows, less fields
Generally speaking, if you need to add a number to a field name then you're doing it wrong. Creating fields for something like color1, color2 for car colors is fine. Doing something like carmod1, carmod2, ... carmod13 is not. At any moment a vehicle may have 1 to 13 mods applied, but it will always have only 2 colors.

More tables
This is where relations come in. To optimize the example I just showed you, we will need two tables. The main table - we will call this the parent table - will store the vehicle's main information like spawnpos, modelid and so on. The second table - the child table - only contains the carmods, in addition to a reference to the parent table.

Parent table structure
Table name: vehicle_info
Structure:

Field name	r_id	modelid	spawnx	spawny	spawnz	spawna	col1	col2
Field type	int(11)	smallint(3)	float	float	float	float	tinyint(3)	tinyint(3)
Attributes	unsigned not null auto_increment primary_key	unsigned not null	not null	not null	not null	not null	unsigned not null	unsigned not null

You will notice that I'm not using the generic integer type. Using smaller types saves space in the long run. An unsigned tinyint can save values between 0 and 255, which is just enough for the amount of carcolors there are.

Child table structure
Table name: vehicle_mods
Structure:

Field name	r_id	mod_model
Field type	int(11)	smallint(5)
Attributes	unsigned primary key	unsigned primary key

You see that this design is much more compact as we now only need 2 fields instead of 13. When a mod is added to a vehicle a row is inserted into the table. Similarly, when a mod is deleted from the vehicle, the corresponding row is deleted from the table.

Since each row is unique (a vehicle can't have the same mod applied twice) we will use both fields as the primary key. This will prevent duplicate entires.

Creating relations
Now, up to creating the relation between the tables. If a vehicle is deleted then we don't want orphaned rows in the child table (that is: referencing a vehicle that doesn't exist in the parent table). The most obvious way would be to send a query to delete these rows, right? Wrong. MySQL can automatically delete (or update) these rows when the referenced row in the parent table is deleted (or updated).

The child table always references the parent table, so that makes up for this query:

PHP Code:

ALTER TABLE vehicle_mods 
ADD FOREIGN KEY (r_id) 
REFERENCES vehicle_info (r_id) 
ON UPDATE CASCADE 
ON DELETE CASCADE 


The relation may also be added directly when the table is created by using REFERENCES ... after the field declaration. The type and attributes of the fields that are being linked must be exactly the same, otherwise it won't work. There also may not be any data in the table that would conflict with the creation of the key.

There are four types of clauses you can use for ON DELETE and ON UPDATE:

• RESTRICT: Trying to delete or update a referenced key in the parent table will fail as long as there are still records in the child table that are linking to the key in the parent table. This is the default.
• NO ACTION: Same as restrict
• CASCADE: If the referenced key is updated or deleted in the parent table then the change will be reflected to the child table. Best option, usually.
• SET NULL: If the referenced key is updated or deleted in the parent table then the engine will set the referee keys to NULL. This requires that the field accepts NULL values. Not very useful.

Be VERY careful with ON DELETE CASCADE, though. For example, if you're linking a user to a house: if the user is deleted then the house is also deleted. Most likely not what you want.

Verification
To verify that stuff works, put some data in both tables. Start with the parent table. Use the r_id that you just obtained - by means of auto_increment - to insert stuff into the child table. Now delete the row in the parent table. If all went well, the corresponding row in the child table should have vanished.

Lastly ...
Ironically, although this tutorial is about database design, the design of this thread leaves much to be desired. Please let me know if you have any questions, or additions.