Database design question

Question

I have a table DEFINITIONS where I store my xml definitions form my components (form,layout,grid...) Then I have detail table PROFILES (1:M relationship) where I store my user profiles if there are any. This user profiles are only allowed if a component is of type grid.

So far I've created just table DEFINITIONS and a table PROFILES in 1:M relationship to the DEFINITIONS table.

I'm just wondering if there is a more suitable design for this situation. I'm worried about the data integrity. There could be other component's (form,layout) PK in PROFILES FK field.

Is this a well-founded concern ?

Answer 1

Rather than storing all the definitions in one table and only allowing PROFILES to link to the grid types, why not separate out the different definitions in to their own tables?

You would then have the tables Form_Definitons, Layout_Definitions, Grid_Definitions etc. and you can create a relationship to your Profiles table only from the Grid_Definitions table.

When you want to retrieve all the definitions you can create a view to union all the xxx_Definition tables together.

Answer 2

For integrity you can use composite FK + CHECK constraint here:

CREATE TABLE Profiles(
   definition_id  INTEGER,
   component_type INTEGER DEFAULT 1 CHECK( component_type = 1 )
);

ALTER TABLE Profiles ADD (
   CONSTRAINT Profiles_FK_Definition 
      FOREIGN KEY (definition_id, component_type) 
      REFERENCES Definitions(definition_id, component_type)
);

Ideally, if it doesn't kill performance, I would always do everything to preserve integrity.
OTOH realistically, if user can't access underlying DB another way but your UI, I wouldn't care much