CODEWITHSUNDEEP
sqldatabase-designentity-relationshipnormalization
user1464296
user1464296

Reputation:

Can Database Normalization occur from values

Say I want to normalize a table

itemID | itemDate | itemSource | type | color | size | material
 254    03/08/1988    toyCo      doll   null     16    plastic
 255    03/08/1988    toyCo      car    blue     null  plastic
 256    03/08/1988    toyCo      boat   purple   20    wood

Now the type field can only have 1 of 3 values. doll, car, or boat. Attributes of color, size, and material are functionally dependent on type. As you can see though, items of type|doll do not determine color. I do not know if this is a problem. But moving on.

type(pk) | color | size | material = table A

itemID(pk) | itemDate | itemSource = table B

We are now in 1nf. My question is, can the type key, along with its attributes, become based on the type keys' possible values?

typeDoll(pk) | size | material = table C

typeCar(pk) | color| material = table D

typeBoat(pk) | color | size | material table E

Upvotes: 0

Views: 78

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95642

I'm not sure I understand exactly what you're asking, but here's one approach to creating an exclusive arc in SQL.

-- Columns common to all types.
create table items (
  item_id integer primary key,
  item_type varchar(10) not null
    check (item_type in 'doll', 'car', 'boat'),

  -- This constraint lets the pair of columns be the target of a foreign key reference.
  unique (item_id, item_type),

  item_date date not null default current_date,
  item_source varchar(25) not null
);

-- Columns unique to dolls. I'd assume that "size" means one thing when you're
-- talking about dolls, and something slightly different when you're talking
-- about boats.
create table dolls (
  item_id integer primary key,
  item_type varchar(10) not null default 'doll'
    check(item_type = 'doll'),
  foreign key (item_id, item_type) references items (item_id, item_type),
  doll_size integer not null 
    check(doll_size between 1 and 20),
  doll_material varchar(25) not null  -- In production, probably references a table 
                                      -- of valid doll materials.
);

The column dolls.item_type, along with its CHECK constraint and the foreign key reference, guarantees that

  • every row in "dolls" has a matching row in "items", and
  • that matching row is also about dolls. (Not about boats or cars.)

Tables for boats and cars are similar.

If you have to implement this in MySQL, you'll have to replace the CHECK constraints, because MySQL doesn't enforce CHECK constraints. In some cases, you can replace them with a foreign key reference to a tiny table. In other cases, you might have to write a trigger.

Upvotes: 2

user1464296
user1464296

Reputation:

What I am trying to achieve is called Polymorphic Association. This can be accomplished by creating a super table to store all possible columns and using a second and third table to constrain foreign keys to primary keys.

Its explained in detail here

Upvotes: 0

Related Questions