Is a master table with only one column necessary in this mysql example?

Question

I have a mysql DB with a table that holds version information for multiple other tables. In order to link to the same family of versions I have a version_master table that holds a primary key to the family of versions that the link refers to. I was wondering if there was a more elegant solution without the need for a version_master table.

CREATE TABLE IF NOT EXISTS `version` (
`version_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`version_master_id` int(10) unsigned NOT NULL,
`major` int(10) unsigned NOT NULL DEFAULT '0',
`minor` int(10) unsigned NOT NULL DEFAULT '0',
`patch` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`version_id`));

CREATE TABLE IF NOT EXISTS `version_master` (
`version_master_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE IF NOT EXISTS `needs_versions` (
`needs_versions_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
`description` text COLLATE utf8_unicode_ci NOT NULL,
`version_master_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`needs_versions_id`));

Answer 1

In this example you can certainly eliminate the version_master table and use the combination of version_id and version_master_id fields as an index. I think you can just drop it, because nothing seem to refer to it with a foreign key.

However, having version_master would be a good idea if you had additional information associated with each family of versions.

Also, you are trying to make a primary key out of the undefined column offer_type_id. It is not clear whether you can logically merge needs_versions with version_master or not. The name itself is not very descriptive. I would recommend not to use verbs in table names.

Answer 2

The other common way to do this is to use SEQUENCEs.

But MySQL does not seem to support them, at least the MySQL manual contains a section on how to simulate sequences using a one row, one column table:

Create a table to hold the sequence counter and initialize it:

CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);

Use the table to generate sequence numbers like this:

UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();