SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Tutorials

Reply
 
Thread Tools Display Modes
Old 04/03/2013, 05:19 PM   #1
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 10,625
Reputation: 2473
Default [MySQL] Table structure and foreign keys

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

Last edited by Vince; 20/11/2015 at 06:05 PM. Reason: Update: index -> primary key
Vince is offline   Reply With Quote
Old 04/03/2013, 05:35 PM   #2
Misiur
High-roller
 
Misiur's Avatar
 
Join Date: Jul 2009
Location: Poland
Posts: 2,201
Reputation: 390
Default Re: [MySQL] Table structure and foreign keys

Good tutorial. SERIAL alias is nice for ids (however it uses bigint, so your is better for most cases). One thing though: NO ACTION - it really means same thing as restrict?

#sidenote:
Example of fetching related data in single query (all mods for vehicle at specific spawnpoint)
Quote:
SELECT t0.mod_model FROM `vehicle_mods` t0 LEFT JOIN `vehicle_info` t1 ON t1.r_id = t0.r_id
WHERE t1.spawnx = %.2f AND t1.spawny = %.2f AND t1.spawnz = %.2f
Load vehicle models and check optionally if the vehicle has mod with id 512351
Quote:
SELECT vi.r_id, vi.modelid, vm.mod_model FROM `vehicle_info` vi LEFT JOIN `vehicle_mods` vm ON vm.r_id = vi.r_id AND vm.mod_model = 512351
Yup - no where clause, or it won't return vehicle data. If there isn't mod with id 512351, then the vm.mod_model will return null.

Moar info to be found via google.
Misiur is offline   Reply With Quote
Old 04/03/2013, 06:00 PM   #3
KyleSmith
High-roller
 
KyleSmith's Avatar
 
Join Date: Mar 2009
Location: United Kingdom
Posts: 2,373
Reputation: 284
Default Re: [MySQL] Table structure and foreign keys

Nice, I was hoping you would make this for foreign keys...

Edit: I was hoping that you would do the tutorial based on 2 tables such as one for `users` and one for `playerweapons` which are two tables...
KyleSmith is offline   Reply With Quote
Old 04/03/2013, 07:26 PM   #4
RealCop228
Godfather
 
Join Date: Feb 2010
Location: Springboro, OH
Posts: 8,161
Reputation: 685
Default Re: [MySQL] Table structure and foreign keys

Finally someone who understands the REAL uses of MySQL!

Nice tutorial, Vince!
RealCop228 is offline   Reply With Quote
Old 05/03/2013, 07:00 PM   #5
nGen.SoNNy
Gangsta
 
nGen.SoNNy's Avatar
 
Join Date: Sep 2009
Location: Romania
Posts: 990
Reputation: 61
Default Re: [MySQL] Table structure and foreign keys

Really nice job and I like verry much that thing with the child table xD. I learned about things like this at school but only in microsoft office )
__________________
If i've helped you, please click
nGen.SoNNy is offline   Reply With Quote
Old 06/03/2013, 12:48 PM   #6
Stylock
Huge Clucker
 
Stylock's Avatar
 
Join Date: Dec 2010
Posts: 327
Reputation: 143
Default Re: [MySQL] Table structure and foreign keys

One of the best MySQL tuts around, but I think it's missing a few essential things:
  • relationship types
  • foreign key indexing
  • clustered index
I've been taught that every table should have a clustered index, especially when foreign keys are involved. A useful quote:
Quote:
Originally Posted by Brad McGehee
As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases–such as an identity column, or some other column where the value is increasing–and is unique. In many cases, the primary key is the ideal column for a clustered index.
http://www.sql-server-performance.co...tered-indexes/
Also, If anyone's interested, there's a visual database design tool called MySQL Workbench, which is perfect for designing relational databases.
Stylock is offline   Reply With Quote
Old 06/03/2013, 01:05 PM   #7
Sinner
Gangsta
 
Sinner's Avatar
 
Join Date: Aug 2010
Location: On This Mortal Coil
Posts: 823
Reputation: 292
Default Re: [MySQL] Table structure and foreign keys

Quote:
Originally Posted by YJIET View Post
One of the best MySQL tuts around, but I think it's missing a few essential things:
  • relationship types
  • foreign key indexing
  • clustered index
I've been taught that every table should have a clustered index, especially when foreign keys are involved. A useful quote:

Also, If anyone's interested, there's a visual database design tool called MySQL Workbench, which is perfect for designing relational databases.
I'm pretty sure indexed are added automatically by most database systems. I'm entirely sure for MySQL and Postgresql. You make a fair point, they do make a huge difference.

@OP

You should tell something about the good practices when creating queries. Things people should avoid (like using IN, SET operators, explicit joins, LIMIT, etc...) + why you should avoid subqueries (they are always slower) and things people should definately use or atleast attempt to use (like JOINs and GROUP BY). If a query is properly built up it can be executed 100x faster.

My course material explained it really well, unfortunately it's in dutch. I found this though: http://docs.oracle.com/cd/B10501_01/...1016.htm#28529
__________________
Please use the search feature before asking a question.
Sinner is offline   Reply With Quote
Old 06/03/2013, 01:08 PM   #8
Yves
Banned
 
Join Date: Feb 2013
Location: Area 51
Posts: 1,653
Reputation: 122
Default Re: [MySQL] Table structure and foreign keys

nice tut very good this will sure help alot of ppl now with using mysql datebase
Yves is offline   Reply With Quote
Old 06/03/2013, 04:31 PM   #9
Bluec0de
Little Clucker
 
Bluec0de's Avatar
 
Join Date: Jan 2013
Location: Italy
Posts: 11
Reputation: 3
Default Re: [MySQL] Table structure and foreign keys

nice tutorial, will be useful
__________________
ItaliaFreeroam 0.3x Server 198.20.110.135:7778

Bluec0de is offline   Reply With Quote
Old 06/03/2013, 04:32 PM   #10
Stylock
Huge Clucker
 
Stylock's Avatar
 
Join Date: Dec 2010
Posts: 327
Reputation: 143
Default Re: [MySQL] Table structure and foreign keys

Quote:
Originally Posted by Sinner View Post
I'm pretty sure indexed are added automatically by most database systems. I'm entirely sure for MySQL and Postgresql. You make a fair point, they do make a huge difference.
I know that primary key constraints create clustered indexes automatically, but I wasn't sure about foreign key indexes. I thought it was MySQL Workbench that was automatically creating indexes for me, but it was probably MySQL that was actually doing it.
Stylock is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Table JaKe Elite Scripting Help 5 13/11/2012 07:53 AM
MySQL: Update all values in table from another table kurta999 Scripting Help 8 23/08/2012 08:55 PM
MYSQL Table thefatshizms Scripting Help 3 31/07/2012 02:06 AM
mysql table bug juraska Scripting Help 4 15/12/2011 07:34 AM


All times are GMT. The time now is 01:27 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.