Problem with referential integrity and baunded values

[kyklops]

Hi everyone!
I'm currently designing a database for an inspection body that performs controls and inspections of wiring systems. The price for the inspection depends on the power of the wiring.
I'm experiencing some problem in the design of the price list for the DB. The actual price list has power brackets and a price value for each of these brackets. If I write the price list table and the wiring table as:

PriceList([U]Bracket[/U], PowerMin, PowerMax, Price)
Wiring ([U]WiringID[/U], Power)

I'm unable to define a referential integrity between the two tables in order to define the price for the inspection.
How can I solve the problem?
Thank you in advance and sorry for the bad English!

[adam_k]

You don't need a referential integrity in order to define the price. You would need a join. Referential integrity ensures that when you are inserting a record in wiring table for example there is a matching record in PriceList and/or that when you delete a record from PriceList all records in Wiring will be deleted with it.

When joining 2 tables you can set a relationship like where power > powermin and power < powermax

Let us know if this has helped you and if you require further assistance make sure you mention what db you are using.
Good luck

[kyklops]

Thank you Adam, that helped me a lot.
Of course the existence of the rows in both tables is indipendent from the rows in the other table.
I'm going to use mySQL, but I'm still designing the DB on paper using an ER diagram.
Thank you again.

[BitBlt]

@adam_k's advice is IMHO correct for small-scale apps.

This scenario is a data administrator's nightmare called a fuzzy relation. It's not really a relation at all strictly speaking. It depends on evaluating values in order to establish relevance to a set. It doesn't really belong in your data structure, and frankly it shouldn't even be in a single statement...getting the price data based on a value range is a programmatic task that should require two SQL statements.

A sample select from @adam_k's suggestion would look something like this:

select * from wiring
left join pricelist
on Power >= PowerMin 
and Power <= PowerMax

As you can see, this statement would cause a cartesian product which would then have to be constrained by the values in Power, PowerMin and PowerMax. Ewww. Very expensive query to run. If you had a lot of data this would slow your app to a crawl. For small amounts of data it would be okay, but not good practice.

What follows this as a sort of "academic exercise" so you understand the issues. My guess is that it is overkill for your scenario, so feel free to disregard it.

If you really want to nail down the relation between Wiring and PriceList, you could do something like:

PriceList([U]Bracket[/U], PowerMin, PowerMax, Price)
Wiring ([U]WiringID[/U], [I]Bracket (fk to PriceList)[/I], Power)

This enforces actual referential integrity between your Wiring and your PriceList, allows you to keep your discrete ranges on your price list, and keep your actual Power number for the Wiring.

Bad news is you have to programatically assign the proper Bracket, either in your code (combo box or selection list?), or a stored proc or a trigger. However, once it's assigned you don't have to worry about doing the comparison later, just follow the foreign key. This might become significant if you are dealing with large quantities of data.

Again, probably overkill but what the heck...we're all about learning here, right?

Anyway, good luck!

[ChrisPadgham]

hmmm not really fully normalised though. How many Min/Max power combinations could you have? not that many I would have thought.

If you are going down this path, why would you not just store the price rather than the Bracket id in the Wiring table. then you would not need to join the other table at all

[kyklops]

@BitBlt
That was a very good answer and I'll probably go for your solution.

@ChrisPadgham
Storing the price in the Wiring table requires someone to read it from another source (a sheet of paper?) before inserting it for each row.
In fact I'm trying to liberate the inspection body from paper.
The solution BitBlt proposed lets me use a combobox, at least.

Anyway, I think I can solve the thread.
Thank you everyone!

[ChrisPadgham]

No you would read it from the PriceList table the same as you would the bracket id