efficient database design inheritance

Question

I want to design a database with have 2 categories. There are 2 subcategories in each category, but they are very similar to other category like:

preventive equipment maintenance:

cat1
- id, equipment,model, series, accesories,date, cost, status
cat 2
- id, equipment,model, series, accesories,date, cost, status

corrective equipment maintenance

cat1
- id, new equipment, old equipment, borreowed equipment, description
cat 2
- id, new equipment,model, series, accesories,date, cost, old equipment, borreowed equipment, description

So as you can see the only diference between data collected on preventive equipment maintenance is the cat (either cat1 or cat2). To solve this I thought to make a table like

CREATE TABLE `preventive_e`(
      id          INTEGER  NOT NULL PRIMARY KEY,
      equipment   VARCHAR(25) ,
      cat         VARCHAR(4) ,      
      CONSTRAINT `uc_Info_E` UNIQUE (`id`)           
);
INSERT INTO `preventive_e` values (1,'nintendo','cat1');
INSERT INTO `preventive_e` values (2,'psp','cat2');

Now in corrective it would be the same, however they are not the same fields, they are almost the same fields, if is cat1 i want to store only some fields, but if is cat 2 i want to store same fields but some more fields

Is there a way to use inheritance or something?, extending the fields but being able to add more particular fields? How would a query look like.

sqlfiddle

why declare a field that only contains 4 char strings as varchar(50)? char(4) seems more appropriate. — Mitch Wheat
– Mitch Wheat, Commented May 14, 2013 at 0:19
i posted an answer but reading the question again and we need more description about what is related to what. equipment has exactly 2 categories? equipment has maintenance? preventative, corrective , both or neither? an category is described by id, new equipment, old equipment, borreowed equipment, description? or is that a list of example categories? — gillyspy
– gillyspy, Commented May 14, 2013 at 2:40
You're actually trying to normalize your tables, which is a good thing, but it's hard to follow what you're asking. — vol7ron
– vol7ron, Commented May 14, 2013 at 2:52

gillyspy · Accepted Answer · 2013-05-14 02:33:31Z

1

why not call the table something generic to both like equipment_maint and have a column acting as a flag for it's usage.

Fiddle

CREATE TABLE `equipment_maint`(
  id          INTEGER  NOT NULL PRIMARY KEY,
  equipment   VARCHAR(25)  ,
  cat         VARCHAR(50) ,  
  type       varchar(20),  /* usage on type of maintenance */
  CONSTRAINT `uc_Info_E` UNIQUE (`id`)           
);

answered May 14, 2013 at 2:33

gillyspy

1,6089 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

efficient database design inheritance

preventive equipment maintenance:

corrective equipment maintenance

1 Answer 1

Fiddle

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

preventive equipment maintenance:

corrective equipment maintenance

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related