| Author |
Topic |
|
CarlMeis
Starting Member
6 Posts |
 Posted - 2006-03-01 : 21:32:02
|
| This problem comes up over and over in my designs. It must be the way I think. Here is an example: Table proposals is the depository of all data. The other table is only to enforce the constraint. An author can make many proposals with the same topic, language and category. When done, she flags just one within this group of rows as FinalizedProposal. I am keeping the data of the ‘drafts’. They are all proposal rows but just one is labeled finalized. For this topic and this language and this category AND THIS AUTHOR there MUST be just one finalized proposal. The drafts are not really historical data because the finalized may be older than some other proposals, so the datetime/timestamp is not the criterion for finalizing. It is explicitly flagging one. So adding a bit column Finalized would not work. For ten proposals meeting the filter one can be finalized=1 and 9 others would be finalized= 0. The unique constraint in the proposals table would not let me keep more than one drafts (finalized= 0). So I create another table for FinalizedProposals and there I implement the constraint. CREATE TABLE proposals( proposalID int NOT NULL, TopicID int NOT NULL, LanguageID int NOT NULL, CategoryID int NOT NULL, AuthorID int NOT NULL, proposalContent varchar(max) NOT NULL, CONSTRAINT PK_ proposals PRIMARY KEY CLUSTERED (proposalID ASC) ) --BTW I don’t need a primary key at all in this foreign key table, just the composite unique key. -- notice the key includes the AuthoID, as there can be another proposal with the same values for the other columns in the key but different AuthorID. CREATE TABLE FinalizedProposals( proposalID int NOT NULL, TopicID int NOT NULL, LanguageID int NOT NULL, CategoryID int NOT NULL, AuthorID int NOT NULL, CONSTRAINT UK_ FinalizedProposals UNIQUE NONCLUSTERED (TopicID ASC, LanguageID ASC, CategoryID ASC, AuthorID ASC) ) It works. But then I look at that table and say: ”Holy schmoly! So many columns repeat. This can’t be good design.” Especially if I need to repeat the same logic with another flag which uses different columns for the unique constraint: --notice the authorID is not included as the approved is just one across the board. CREATE TABLE ApprovedProposals( proposalID int NOT NULL, TopicID int NOT NULL, LanguageID int NOT NULL, CategoryID int NOT NULL, AuthorID int NOT NULL, CONSTRAINT UK_ ApprovedProposals UNIQUE NONCLUSTERED (TopicID ASC, LanguageID ASC, CategoryID ASC) ) This time there could be many finalized proposals submitted by different authors and only one can be flagged “approved”. I still need to enforce the constraint and if I do it I end up with another table. These start looking awful lot like views in the sense that they are representations of filtered data that is contained in the original table. Doing a flag table and have the “approved” and “finalized” flags be rows in that table does not help me enforce constraints on the subsets of flagged items. I could with junction table, but it would have to include the other columns that participate in the unique key. Since the columns differ, I would need another junction table for each flag, so I don’t really gain anything, and probably lose readability since the filters are now values of fields in the flag table instead of the table names. Is there a slicker way to do this? |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-03-01 : 22:06:53
|
| How about this.. go with 1 Proposal table and add a field called ProposalStatusID INTCreate a table called ProposalStatus(ProposalStatusID INT IDENTITY(?) NOT NULL,StatusDesc VARCHAR(xxx))Populate accordingly (proposal, finalized, approved, etc..)Build logic into your app or update proc to check for your unique requirements. |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-03-01 : 23:20:39
|
quote:
Build logic into your app or update proc to check for your unique requirements.
Anything but that!There are 3 ways to go for this contraint..Write a trigger, write a CHECK constraint using a UDF or a computed column with a unique constraintDavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-03-02 : 00:06:30
|
| lol!Gonna be kind of an ugly trigger isn't it, not much different than the update proc, but I guess more reliable. I tend to stay away from triggers if I can.Check constraint with a UDF. Aren't you going to run into an issue with the check constraint when an author makes multiple proposals? I guess I'm not getting what you'd do in the UDF.Computed column. Same question as the check constraint.BTW not sure if that's your usual auto signature, but very appropriate. |
|
|
|
CarlMeis
Starting Member
6 Posts |
Posted - 2006-03-02 : 00:53:29
|
| . |
|
|
|
CarlMeis
Starting Member
6 Posts |
Posted - 2006-03-02 : 00:56:24
|
| Thank you all for responding.David,How would you do a check constraint? Would you mind posting a snippet?Carl |
|
|
|
CarlMeis
Starting Member
6 Posts |
Posted - 2006-03-02 : 01:16:22
|
| Joe,I am a newbie and my opinions come from common sense rather than knowledge, but I am kind of leery letting the DML code do the data integrity for me. What if data gets added to in other ways than the designated sproc?What I am describing is a constraint and it best reside in the table and not in external code, if it can be helped.Is the solution I posted that outlandish or is it a common way to solve this?Carl |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
|
|
JoeNak
Constraint Violating Yak Guru
292 Posts |
Posted - 2006-03-02 : 10:54:54
|
Carl,Yes, DML is not the best way to handle data integrity. Take a look at the links to David's weblog, they're very good articles.quote:
Is the solution I posted that outlandish or is it a common way to solve this.
The 3 table solution? I've seen worse.quote:
If you are used to spoon feeding the data into the database, then set based constraints can seem at bit ugly.
David, great links. We should definetly use constraints more, I think I'm picking up bad habits in my current work environ. |
|
|
|
CarlMeis
Starting Member
6 Posts |
|
|
|
How to design flag for certain rows?