May 21, 2009 at 3:43 pm
Where I work as DBA, I have an interesting problem. I was able to solve it, but I don't LOVE the solution. Here is where you guys come in.
A part of the ERD contains the following tables:
Model
ModelOption (FK to Model, PK for ModelOptionAttributeValue)
Attribute
AttributeValue (FK to Attribute, PK for ModelOptionAttributeValue)
ModelOptionAttributeValue
You can think of them like cars. The Model is Charger. The ModelOption is 'SXT'. The ModelOption is really just a group of Attribute Values, so that the group of attribute values can be costed together.
Attributes are things like EngineSize or Color. Attribute Values are things like 6-cyl and Hemi for the EngineSize, Black and Red for Color, etc.
Basically, when someone buys a car, we determine their group of Attribute Values they have chosen. Those Attribute Values have cost associated to them, so we can tell them exactly what the car will cost.
We then want to save those Attribute Values in the ModelOptionAttributeValue table, so that group of Attribute Values will stay linked to that Model Option.
Here is the catch: we never want to store the exact same group of Attribute Values again. If we did, we might cost them differently, and we would have no idea which one was correct.
So, I want to implement a unique key on the ModelOptionAttributeValue table, but I can't figure out how. How do I write a constraint that says "Don't allow anyone to save 'Black' and 'Hemi' for a Charger again".
-----------------------------
I enjoy queries!
May 21, 2009 at 5:24 pm
Two simple ways from me. There might be more elegant, but off the top of my head.
1. control access to saving by removing insert for users on the tables, use a stored proc, give execute permissions. Check with an exists (select ...) before inserting.
Not great DRI, but it can work.
2. Put the model, attribute, and attrvalue in the same table. You can still store lookups elsewhere, but keep them all together and insert to the single table, use a trigger or batch process to put them in other tables if they don't exist there.
May 22, 2009 at 7:47 am
I can handle the no-dupes thing from the application - that's how I'm doing it now. A better solution would seem to be a database constraint of some kind, but I don't know how to implement such a "grouped constraint," if you will.
-----------------------------
I enjoy queries!
June 1, 2009 at 10:48 am
Any other suggestions?
-----------------------------
I enjoy queries!
June 1, 2009 at 11:00 am
I guess I'd want to see the DDL for the tables and some sample data for them as well. This way I could see what you are asking rather than trying to visualize it based solely on your description of the problem. The devil is really in the details, and you are just providing a 30,000 foot view.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply