CODEWITHSUNDEEP
databasedatabase-designnormalizationdatabase-normalization
onassar
onassar

Reputation: 3568

DB Normalization and lookup tables

Doing some DB thinking, and have a basic table with, for example, a links table. Links could have a foreign key pointing to an account object/record. However, for the sake of simplicity and abstraction (eg. treating a link as a content-based-resource), I thought I would assign the link to an account via a general lookup table (eg. called lookups, not account_links or link_accounts).

While a link can only ever be assigned to one account (and must always be assigned to one account), the inner-me wants to create that foreign key.

But I really like the concept of abstracting out an object/data-resource and decoupling it's context (eg. it's assigned to an account, or a user, or whatever).

Would appreciate some thoughts if anyone's got em :)

schemas:

links:

CREATE TABLE `links` (
  `link_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp_updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `timestamp_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8_unicode_ci NOT NULL,
  `resource` longtext COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`link_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Links';

lookups:

CREATE TABLE `lookups` (
  `lookup_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp_updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `timestamp_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `source_node_type` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `source_node_id` int(10) unsigned NOT NULL,
  `target_node_type` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `target_node_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`lookup_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Obejct Assignments';

Upvotes: 1

Views: 559

Answers (1)

user45886
user45886

Reputation:

So you want to store the links without adding a foreign key cause that would force you to link it to say an account -OR- a user since the FK would point to one specific table?

You could store the link value alone in it's own 'lookup' table as PK or with a unique index. Then FK from your user, account and what else. This will introduce sharing though, you'd have to add some constraints to make sure you can't assign a link if it's already in the 'lookup' table.

Add multiple nullable FKs on the lookup table is possible too, perhaps less generic. Add a contraint to make sure at least one and at most one of the FK's is set to a value.

Upvotes: 1

Related Questions