Which IDs from a database to use?

Question

I have two SQL tables, A and B. Each table has primary key ID. The table B has field X (which may be NULL) which refers to an ID of A (if it is not null).

Then I need a list (stored in a file) of identifiers of objects stored in table A.

Which IDs do you suggest to use, A.ID or B.ID in the list? (It is clear that having the ID of a row in B we can infer the ID of a row in A, provided this row in B has non-NULL X.)

[ADDED] To be clear: An object is partially stored in A and partially in B.

[ADDED] Structure of the tables (z_users is B, z_clients is A, z_users.client is the field X):

CREATE TABLE `z_users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `password` varchar(255) collate utf8_bin NOT NULL,
  `fullname` varchar(255) collate utf8_bin NOT NULL,
  `phone` varchar(255) collate utf8_bin NOT NULL,
  `address` text collate utf8_bin NOT NULL,
  `email` varchar(255) collate utf8_bin default NULL,
  `admin` enum('false','true') collate utf8_bin NOT NULL default 'false',
  `worker` int(10) unsigned default NULL,
  `client` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `worker` (`worker`),
  UNIQUE KEY `client` (`client`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `z_clients` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `company` text collate utf8_bin NOT NULL,
  `shortdesc` text collate utf8_bin NOT NULL,
  `site` varchar(255) collate utf8_bin NOT NULL,
  `affair` varchar(255) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

nvogel nvogel · Accepted Answer · 2011-07-21T14:24:31

Use the business key (natural key) of your table. It's usually unwise to persist a surrogate key outside the database because it creates a hidden dependency - if the surrogate key is refactored or its values changed then the data outside the database becomes invalid or references the wrong value.

Each of your tables appears to have only one key and it isn't clear to me what the business key(s) are supposed to be in this case.

Which IDs from a database to use?

3 Answers