Vince's Tech Stuff

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!

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 0 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 car mods (plus a reference to the parent table).

Parent table structure

• Table name: Vehicle
• Structure:

field	type	attributes
id	int	unsigned, not null, primary key, auto_increment
modelid	smallint	not null
spawnx	float	not null
spawny	float	not null
spawnz	float	not null
spawna	float	not null
col1	tinyint	unsigned, not null
col2	tinyint	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: VehicleMod
• Structure:

field	type	attributes
vehicleid	int	unsigned, not null
modelid	smallint	unsigned not null

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. Or in other words: if we delete a vehicle we must make sure to delete any associated car mods as well. The most obvious way would be to send a query to delete these rows, right? Not quite. 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:

ALTER TABLE VehicleMod 
ADD CONSTRAINT 
FOREIGN KEY (vehicleid) REFERENCES Vehicle (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 or by adding the whole CONSTRAINT line beneath the field declarations. 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.
• 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.

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