Print out column value of a referenced table

Question

To avoid redundancy, to save up space, for db strength reasons and to easily add new possible values i designed this db layout:

CREATE TABLE IF NOT EXISTS `tmcc_make` (
  `id` tinyint UNSIGNED NOT NULL AUTO_INCREMENT,
  `make` varchar(60) NOT NULL UNIQUE,
  `status` bit DEFAULT NULL COMMENT 'NULL = active | 1 = deprecated',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `tmcc_model` (
  `id` smallint UNSIGNED NOT NULL AUTO_INCREMENT,
  `model` varchar(60) NOT NULL UNIQUE,
  `make` tinyint UNSIGNED NOT NULL,
  `status` bit DEFAULT NULL COMMENT 'NULL = active | 1 = deprecated',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`make`) REFERENCES tmcc_make(`id`)
);

CREATE TABLE IF NOT EXISTS `tmcc_vehicle` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `make` tinyint UNSIGNED NOT NULL,
  `model` smallint UNSIGNED NOT NULL,
  [...] 
  PRIMARY KEY (`id`),
  FOREIGN KEY (`make`) REFERENCES tmcc_make(`id`),
  FOREIGN KEY (`model`) REFERENCES tmcc_model(`id`)
);

Now, i know it would be easier to use the description field as PK of the first 2 tables so that i already have those values ready, but this will use a lot of more space. What is the best and less complex way to retreive those strings matching the id? Do you think it will be better to use more space and directly set the strings instead of an integer pk?

An auto-incremented integer primary key is generally a really good idea. A description field should not be made the primary key, because it could change over time. — Gordon Linoff, Mar 22 '14 at 11:46
@GordonLinoff the description field would be `UNIQUE`, so it is in my opinion good as PK. The only problem i would try to solve, is that this way i will waste a lot more space... — Mariano, Mar 22 '14 at 11:50
http://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables and http://stackoverflow.com/questions/14059705/must-database-primary-keys-be-integers and others like them. — Gordon Linoff, Mar 22 '14 at 11:55
@GordonLinoff ok, anyway this wasn't the originary question and as you can see, my PK is as you said...problem is which is the best and more efficient way to retreive the description associated to that pk...i can only think to a php function to convert that value into the relative string... — Mariano, Mar 22 '14 at 12:26
I'm confused. You know how to create tables with complex foreign key relationships, but you don't know how to use `join`? — Gordon Linoff, Mar 22 '14 at 12:27
`SELECT tmcc_make.make FROM tmcc_vehicle JOIN tmcc_make ON tmcc_vehicle.make = tmcc_make.make` is returning no results... — Mariano, Mar 22 '14 at 12:38

score 1 · Accepted Answer · answered Mar 22 '14 at 12:37

I would suggest a few changes to your data structure. First, do not have different types for columns with the same name in different tables (such as model and make). Also, I prefer to have primary keys identify the entity, so the name matches the foreign key. And finally, you should have make in the vehicle table (unless you do this very intentionally). The structure is vehicle --> model --> make. I suggest this structure:

CREATE TABLE IF NOT EXISTS `tmcc_make` (
  `makeid` tinyint UNSIGNED NOT NULL AUTO_INCREMENT,
  `make` varchar(60) NOT NULL UNIQUE,
  `status` bit DEFAULT NULL COMMENT 'NULL = active | 1 = deprecated',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `tmcc_model` (
  `modelid` smallint UNSIGNED NOT NULL AUTO_INCREMENT,
  `model` varchar(60) NOT NULL UNIQUE,
  `makeid` tinyint UNSIGNED NOT NULL,
  `status` bit DEFAULT NULL COMMENT 'NULL = active | 1 = deprecated',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`makeid`) REFERENCES tmcc_make(`makeid`)
);

CREATE TABLE IF NOT EXISTS `tmcc_vehicle` (
  `vehicleid` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `modelid` smallint UNSIGNED NOT NULL,
  [...] 
  PRIMARY KEY (`vehicleid`),
  FOREIGN KEY (`model`) REFERENCES tmcc_model(`modelid`)
);

Then the query to fetch results is:

select v.id, mo.model, ma.make
from vehicle v join
     tmcc_model mo
     on v.modelid = mo.modelid join
     tmcc_make ma
     on mo.makeid = ma.makeid;

You might also want to store the status in a separate table, particularly if you are going to use the same values in both tables.

your solution makes sense...but why would you store `status` in a different table? both make or model could be deprecated not depending on each other's status...unless you mean for redundancy/storage reason...but i would use the same space as it is a bit type...or maybe i just did not understand your tip — Mariano, Mar 22 '14 at 12:54
@Mariano . . . I mean that you would have a status table with rows like (0, Active), (1, Deprecated) and so on. Then you would have a `statusId` in each table. — Gordon Linoff, Mar 22 '14 at 14:41
ok so i understood what you were talking about...but what's the pro if i do your way instead of mine? not even space gain... — Mariano, Mar 22 '14 at 14:55
@Mariano . . . No space gain. Just a guarantee that the status flags are correctly interpreted and used wherever they occur. — Gordon Linoff, Mar 22 '14 at 14:57

Print out column value of a referenced table

1 Answers1