Your opinion on the relationship design

[El_Pablo]

Here's a dilemna that I have. I see two different solutions, both have pros and cons. I would like to know your opinion.

I am a creating a personnal FM application for car evaluation. People will call to evaluate their car.

To evaluate the value a car I need the subject description (wheel type, engine, exhaust, body condition, paint, ...) and comparables which are sells on the market.

A subject need at least one comparable, but usually more to give a certain value to the car. A sell can be use for many subject.

For now I know that there are at least 3 tables.

Subject (pkSubjectID, ...)

Sell (pkSellID, ....)

Comparable (pkComparableID, fkSubjectID, fkSellID ,...)

So here's the dilemna, for each comparable I need to make some value adjustments because each sell used is not exactly the same as the subject. So the adjustment can be on the wheels, the body, the paint job, ...

Here's an example of adjustment :

The wheels on the subject are 16 inches and have a default value of 2000$, but one of my sells have 18 inches wheels which are valued 3000$ so a difference of +1000$ vs the subject. On another of my comparables the wheel are 16 inches but are valued 1500$ so the adjustment is -500$.

In this case, the value of the wheels on the subject will be 2000 + (1000 + -500) / 2 = 2 250$.

There's more than one kind of adjustment possible.

Now the dilemna is how do I design the tables?

Solution 1

Do I create 2 more tables which are:

adjustmentType (pkAdjustmentTypeID, defaultValue, ...)

adjustment (pkAdjustmentID, fkComparableID, fkAdjustmentTypeID, value, ...)

See the diagram here

or

Solution 2

I create only adjustment table and add fixed the necessary field in comparable for each adjustment?

Comparable (pkComparableID, fkSubjectID, fkSellID, fkWheelAdjID, fkBodyAdjID, ...)

Diagram here

Here are the pros and cons

Solution 1

Pros Easy maintenance, relational, a new type of adjustment is only a new record in the adjustmentType table.

Cons How to create a calculated field for a specific adjustment in the subject?

Solution 2

Pros Easy to create a calculated field in the subject for the adjustments.

Cons Not so relationnal, hard to maintain, each new adjustment is a new field in the subject and the comparable.

What are your opinions?

[El_Pablo]

If I use solution 1, how can I average let say the wheel adjustement for the subject other than scripting?

Edited September 16, 2008 by Guest