Re: Design for Category Feature

You need to fix you data element names. There is no such thing as a
"name_id" or just a "category".

CREATE TABLE Persons
(person_id INTEGER NOT NULL PRIMARY KEY
first_name VARCHAR (30) NOT NULL,
surname VARCHAR (30) NOT NULL,
etc... );

CREATE TABLE Contacts
(contact_category INTEGER NOT NULL
REFERENCES ContactCategories (contact_category)
ON UPDATE CASCADE
ON DELETE CASCADE,
person_id INTEGER NOT NULL
REFERENCES Persons(person_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (contact_category, person_id));

CREATE TABLE ContactCategories
(contact_category INTEGER NOT NULL PRIMARY KEY,
contact_description VARCHAR(15));

Or if you have only a few categories that do not change very often:

CREATE TABLE Contacts
(contact_category INTEGER NOT NULL
CHECK (contact_category IN ('friend;, 'enemy', ..)),
person_id INTEGER NOT NULL
REFERENCES Persons(person_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (contact_category, person_id));

.

Relevant Pages

  • Re: object model to table design mapping problem
    ... the INCITS H2 Database Standards Committee(nee ANSI X3H2 ... NOT NULL PRIMARY KEY, ... REFERENCES Courses, ... ON UPDATE CASCADE ...
    (microsoft.public.sqlserver.programming)
  • Re: String Search
    ... Sample data is also a good idea, ... (proj_nbr INTEGER NOT NULL PRIMARY KEY, ... ON UPDATE CASCADE ... REFERENCES Projects ...
    (microsoft.public.sqlserver.programming)
  • Re: Selecting several types
    ... Please post DDL, so that people do not have to guess what the keys, ... NOT NULL PRIMARY KEY, --let's use GTIN ... ON UPDATE CASCADE, ... REFERENCES ItemTypes ...
    (comp.databases.ms-sqlserver)
  • Re: Cascading update on primary key
    ... primary key depends on one of the 2 secondary tables. ... (mom CHAR(5) ... REFERENCES Mothers ... ON UPDATE CASCADE, ...
    (microsoft.public.sqlserver.programming)
  • Re: How do I discover a tables primary key?
    ... sp_helpconstraints system procedure in isql the result looks like ... an index once I have an entry in sysindexes. ... -- Number of references made by this table: ... from within my program I only get back one row, the PRIMARY KEY ...
    (comp.databases.sybase)