CODEWITHSUNDEEP
sqldatabase-design
user137348
user137348

Reputation: 10332

Database design 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 ?

Upvotes: 1

Views: 118

Answers (2)

Alexander Malakhov
Alexander Malakhov

Reputation: 3529

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

Upvotes: 1

Tony
Tony

Reputation: 10357

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.

Upvotes: 1

Related Questions