Quick question about relational one-to many database

Question

I'm doing a venue/events database and I've created my tables and would like some confirmation from someone if I did everything right :)

I have 2 tables:

• Venues
• Events

The primary key of Venues is VENUE_ID, which is set to auto_increment. I have the same column in Events, which will contain the number of the Venue ID. This should connect them, right?

Also, the table engine is MyISAM.

Answer 1

It does not automatically link the tables to each others, and the referenced columns don't necessarily have to have the same name (in fact, there are situations where this is impossible: e.g. when a table has two columns that both reference the same column in another table).

Read up on foreign keys; they're standard SQL and do exactly what you want. Note, however, that the MyISAM storage engine cannot enforce foreign key constraints, so as long as any of the tables involved uses MyISAM, the foreign key declaration doesn't add much (it does, however, document the relationship, at least in your SQL scripts).

I suggest you use InnoDB (or, if that's feasible, switch to PostgreSQL - not only does it provide foreign key constraints, it also has full support for transactions, unlike MySQL, which will silently commit a pending transaction whenever you do something that's not supported in a transaction, with potentially devastating results). If you have to / want to use MySQL, I suggest you use InnoDB for everything, unless you know you need the extra performance you can get out of MyISAM and you can afford the caveats. Also keep in mind that migrating large tables from MyISAM to InnoDB later in production can be painful or even outright impossible.

Answer 2

Your db structure is right. You can use Innodb for adding foreign key contraints. Also don't forget to add index to the second table for faster joining two tables.

More info about FK http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Note to comments: Innodb allows you to make concurrent select/(insert/update) but MyIsam allows you to do the same things if you don't delete from MyIsam table. Otherwise MyIsam will lock your whole table.

Answer 3

Generally, yes. This is how you indicate a one-to-many relation between two tables. You may also specifically encode the relationship into the database by setting up a Foreign Key constraint. This will allow add'l logic such as cascading.