How to Handle a Many-to-Many Relationship with PHP and MySQL
By Tony Marston2005-04-06
Database schema
|
|
Let us assume that the existing tables, 'A' and 'B', are defined as follows:
CREATE TABLE `a` (This new table must be constructed to allow the following:
`a_id` varchar(6) NOT NULL default '',
`a_desc` varchar(40) NOT NULL default '',
PRIMARY KEY (`a_id`)
)
CREATE TABLE `b` (
`b_id` varchar(6) NOT NULL default '',
`b_desc` varchar(40) NOT NULL default '',
PRIMARY KEY (`b_id`)
)
* It must have a column which links back to table 'A'.
* It must have a column which links back to table 'B'.
* It must allow no more than one row to exist for any combination of rows from table 'A' and table 'B'.
* It must have a primary key.
In order to satisfy all these rules all I need in table 'X' are two columns, 'a_id' and 'b_id'. I do not need an additional column for a primary key as I can use the existing two columns. This produces a database schema as follows:
CREATE TABLE `x` (Note here that I have created a primary key from two columns, not one. Some naive database designers insist that every database table should have a single-column technical primary key called 'id' which obtains its value from an automatically-incrementing internal counter. In MySQL this can be done with the 'auto-increment' option. While I agree that there are places where a technical primary key is definitely beneficial, in my years of experience I have also encountered instances where a technical primary key is not only unnecessary, it is actually detrimental. A cross-reference table is one of those places.
`a_id` varchar(6) NOT NULL default '',
`b_id` varchar(6) NOT NULL default '',
PRIMARY KEY (`a_id`,`b_id`),
)
Also note that it is not necessary to create a separate index for 'a_id' as this is already covered by virtue of the fact that it forms the leading portion of the primary key. A separate index on 'b_id' might be of benefit depending on the volume of rows in the table and the frequency of searches on that column.
Tutorial Pages:
» Intended Audience
» Introduction
» Database schema
» Form design
» Summary
|
