Hi,

I am currently struggling with a redundancy, I cannot get rid of.
I am trying to create a small ticketing-system.
You can see the current design here

There is one redundancy involved:
The relation between Events and Locations defines, which Seats can be booked per Event. But there is no mechanism the enforces the Ticket to belong to a seat that is part of its event.
In other words: It might happen that there is a ticket in the system that belongs to a seat that is not part of the location the event belongs to.

I don't have the slightest idea, how to resolve that. Do you?

Thank you very much for an answer
Andreas

Start with location:

location (location_id*)

Each location has multiple seats, each seats belongs to one location. Since we don't expect seats to ever change locations, the key of location is taken as part of the key of seat:

seat (location_id*, seat_id*)

For the purposes of this example, we'll assume that an event takes place at one location. Obviously, a location will have multiple events. The structure is similar to seats:

event (location_id*, event_id*)

A ticket, then, is basically an association table relating a seat to an event:

ticket (seat_location_id*, seat_id*, event_location_id*, event_id*)

Since the seat location and event location must be the same, you can simplify this to:

ticket (location_id*, seat_id*, event_id*)

Thank you for your answer
I got your idea on the second glance. Took me a while to see that you are creating the event- and seat-ids per location.

I guess, there is no way to solve this with simple "one-column-keys"?

No easy way, no. This is one reason why "simple one-column keys" aren't always such a neat idea.

I would be interested how this requirement could be modelled with surrogates - do you have something in mind please Tony? I have come across such a requirement a handful of times and have never been able to model it any way other than futurity's method.

I had considered writing up an abstract of such a problem to present to the "surrogates and only surrogates" brigade and ask them how they would solve it but of course I couldn't be arsed.

pootle flump ur codings are working excelent.

Not really. In theory, if any DBMS actually supported ANSI "assertions" you could use one of those - something like:
... but of course they don't. Another possibility is materialized views with constraints - but this is an academic solution rather than one to put into production, perhaps.

In practice it would probably be done using triggers, which is a bit sucky.

Not heard of assertions, but that could be done in SQL Server using a CHECK constraint checking the return of a scalar function.

But all three options you've come up with are, as you imply, inferior to simply designing the thing properly with natural keys. Thanks for the response

pootle flump ur codings are working excelent.

When you insert a row into the Events table a trigger or stored procedure should insert into a Event/Seat table that contains the EventID, SeatID, and a flag that indicates availability (available, sold, reserved). You may want to add price depending on how you are doing pricing. The Ticket should be linked to rows in this table. This way you know what Seats are still available for each event AND you are sure the Seat actually ties to that Event.