What you are proposing to do can only be done with MySQL cleanly under three(3) conditions
- CONDITION #1 : Use the MyISAM storage engine
- CONDITION #2 : Make auto_increment column part of a compound primary key
- CONDITION #3 : Each auto_increment for a given type must exist in its own row
- See the auto_increment documentation for MyISAM
Here is your original table layout
CREATE TABLE `invoices` (
`id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`invoicenumber` mediumint unsigned NOT NULL,
`branch` enum('A','B') NOT NULL,
`date` date NOT NULL,
`client` varchar(100) NOT NULL
) COMMENT='' ENGINE='InnoDB';
Based on the three conditions I just mentioned, here is the new proposed table layout:
CREATE TABLE `invoices` (
`invoicenumber` mediumint unsigned NOT NULL auto_increment,
`branch` enum('A','B') NOT NULL,
`date` date NOT NULL,
`client` varchar(100) NOT NULL,
PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM';
Here is an example via sample data and SQL:
drop database if exists user1162541;
create database user1162541;
use user1162541
CREATE TABLE `invoices` (
`invoicenumber` mediumint unsigned NOT NULL auto_increment,
`branch` enum('A','B') NOT NULL,
`date` date NOT NULL,
`client` varchar(100) NOT NULL,
PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM';
INSERT INTO invoices (branch,date,client) VALUES
('A',DATE(NOW()),'John'),
('B',DATE(NOW()),'Jack'),
('A',DATE(NOW()),'Jeff'),
('B',DATE(NOW()),'Joel'),
('A',DATE(NOW()),'Jane'),
('B',DATE(NOW()),'Joan'),
('A',DATE(NOW()),'June');
SELECT * FROM invoices ORDER BY branch,invoicenumber;
Here it is executed:
mysql> drop database if exists user1162541;
Query OK, 1 row affected (0.01 sec)
mysql> create database user1162541;
Query OK, 1 row affected (0.02 sec)
mysql> use user1162541
Database changed
mysql> CREATE TABLE `invoices` (
-> `invoicenumber` mediumint unsigned NOT NULL auto_increment,
-> `branch` enum('A','B') NOT NULL,
-> `date` date NOT NULL,
-> `client` varchar(100) NOT NULL,
-> PRIMARY KEY (branch,invoicenumber)
-> ) COMMENT='' ENGINE='MyISAM';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO invoices (branch,date,client) VALUES
-> ('A',DATE(NOW()),'John'),
-> ('B',DATE(NOW()),'Jack'),
-> ('A',DATE(NOW()),'Jeff'),
-> ('B',DATE(NOW()),'Joel'),
-> ('A',DATE(NOW()),'Jane'),
-> ('B',DATE(NOW()),'Joan'),
-> ('A',DATE(NOW()),'June');
Query OK, 7 rows affected (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM invoices ORDER BY branch,invoicenumber;
+---------------+--------+------------+--------+
| invoicenumber | branch | date | client |
+---------------+--------+------------+--------+
| 1 | A | 2012-04-21 | John |
| 2 | A | 2012-04-21 | Jeff |
| 3 | A | 2012-04-21 | Jane |
| 4 | A | 2012-04-21 | June |
| 1 | B | 2012-04-21 | Jack |
| 2 | B | 2012-04-21 | Joel |
| 3 | B | 2012-04-21 | Joan |
+---------------+--------+------------+--------+
7 rows in set (0.00 sec)
mysql>
Give it a Try !!!
CAVEAT : At present, only the MyISAM Storage Engine supports multiple auto_increment values grouped with other columns. This is not possible with InnoDB based on auto_increment columns being tied directly to the gen_clust_index (aka Clustered Index) !!!
Best Answer
While it is possible to have a single row for Kingsway and a column for each facility it would be a poor design.
Better to have one table to list clubs. A second table holds the superset of all possible facilities across all clubs. A third table shows which facilities each club actually has. It will have a row for each (club, facility) pair e.g. (Kingsway, pool), (Kingsway, bicycles), (Kingsway, sauna) etc. If another club also had the same facilities there would be a further three rows in this table: (Club2, pool), (Club2, bicycles), (Club2, sauna).
How these tables become populated depends on the source of the data and the particular database design chosen. If the data comes from off-line sources, say brochures, there's no alternative but to type it in to each of the three tables. If the source is digital it could be loaded as-is into a disposable staging table. The appropriate parts are then copied to each of the three data tables. Finally the staging table can be dropped.
The final design could deploy either natural keys or surrogate keys. Using natural keys the third, linking table will appear exactly as shown above. The actual string "Kingsway" and the actual string "pool" will be in the linking table. Using surrogate keys the club and facility tables must be loaded first to creating the pairings between values in columns
club_nameandclub_id, and betweenfacility_nameandfacility_id. Then the linking table can be populated with the correct(club_id, facility_id)pairs. Likely this will need to join the staging table to the club and facility tables.