1

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 ?

Improve this question

2 Answers 2

Reset to default
1

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

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

Comments

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.

Improve this answer

10 Comments

Wouldn't there be to much redundant data ? The check contraint solutions seems to me much better.
Why do you think there would there be redundant data? I'm suggesting you separate out the different types of Definition to allow you to constrain the Profiles to the Grid_Definitions table using a normal foreign key. Out of interest do all Profiles have Definitions or can a profile exist without at least one?
There would be multiple tables with the same structure but different names; it doesn't mean your data is duplicated. And yes, you would have to add a new table if you created a new component. If you are going to be adding components frequently then the constraint based solution provided by Alexander will be fine but you'll have to remember to change the constraint each time you want to allow a Profile to reference another Definition type.
Have a read of this SO question (stackoverflow.com/questions/870808/…) it might help you decide which way to go with your design.
Don't worry, I'm not mad at you. If Alexander's answer fits your purpose you are correct to accept it. I'm just trying to help out :)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.