7

I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them.

I have seats at a theater, the seats are divided into 4 main areas (A, B, C, D). Each area has the same number of rows and the same number of seats per row.

In my database, I'd like to have Row + SeatNumber as a compound primary key, and to have one table for each area.

Now, I don't yet know how I'll do my selects, but what I want to ask: If I do it this way, will my selects be doable ? I want to, for example, select an exact position within the theater (where I know the area, row and seat number).

Would the 4 tables be a hindrance ? Could you give an example of how such a "select" might look ?

P.S. This is my first time at the site, if the question does not belong here, please direct me to a more suitable site within stack exchange.

3
  • 2
    You'd be better off having a single table with the area as another column.
    – Philᵀᴹ
    Apr 14, 2013 at 10:28
  • I need seat number and row (in combination with area) to be unique. This would be easy with PK (row & seat) within different areas (tables) But otherwise how can I do this ? Is there UNIQUE (area, seat row) that makes the combination of 3 unique (not each column individually) ?
    – Kalec
    Apr 14, 2013 at 10:34
  • Use a unique constraint
    – Philᵀᴹ
    Apr 14, 2013 at 10:36

5 Answers 5

18

You should use one table for this, unless you need more stringent constraints.

Each area has the same number of rows and the same number of seats per row.

Let's say there are 5 rows in every area, and 6 seats in every row. You'd want to use something along these lines.

create table seats (
  area char(1) not null check (area in ('A', 'B', 'C', 'D')),
  row integer not null check ( row between 1 and 5 ),
  seat integer not null check ( seat between 1 and 6 ),
  primary key (area, row, seat)
);

To select a single seat, put three values in the WHERE clause.

select *
from seats
where area = 'A' and
       row =  1   and
      seat =  2;

To use a table like this to model seat reservations, populate it with every possible area, row, and seat. Then set a foreign key reference to it.

create table reservations (
  performance_time datetime not null,
  party_name varchar(40) not null,
  area char(1) not null,
  row integer not null,
  seat integer not null,
  primary key (performance_time, party_name, area, row, seat),
  foreign key (area, row, seat) references seats (area, row, seat)
);

You can see all the seating for a performance with this query.

select s.area, s.row, s.seat, r.performance_time, r.party_name
from seats s
left join reservations r
       on r.area = s.area and
          r.row  = s.row  and
          r.seat = s.seat and
          r.performance_time = '2013-04-30 08:00 pm'

And you can get all the available seats for a performance with something along these lines.

with seating as (
  select s.area, s.row, s.seat, r.performance_time, r.party_name
  from seats s
  left join reservations r
         on r.area = s.area and
            r.row  = s.row  and
            r.seat = s.seat and
            r.performance_time = '2013-04-30 08:00 pm'
)
select *
from seating
where performance_time is null

By default, SQL Server will create a clustered index for a primary key constraint. You'll want to give careful thought to the order of columns in your primary key constraints, and consider adding other indexes as well. (Especially since your outputs will often need to be ordered by area, row, and seat.)

8
  • Thank you very much for such a detailed answer. I was expecting something on the lines of "use a unique constraint" (just found out it also works for combinations of two or more columns also).
    – Kalec
    Apr 14, 2013 at 11:56
  • High quality and well thought out answer. Wish more folks responded at this quality. Apr 14, 2013 at 18:52
  • 2
    Thank you, Sid. I hope you know that I'm going to quote you on my CV. Apr 14, 2013 at 19:11
  • Very nice answer :) Apr 14, 2013 at 20:32
  • 1
    Yes, more tables for more referential integrity. (But there's no "areas" table. There could be, though.) Usually you trust the local admins to a certain extent. They have a vested interest in being careful. People who take reservations over the phone wouldn't have insert or update permissions on "seats". Apr 16, 2013 at 11:29
7

If the information for the seat entity is the same per area, then you could simplify the design by having just one Seats table with an Area column.

In this case, the query would look something like:

SELECT ... FROM Seats 
WHERE Seats.Area = 'A' 
AND Seats.Row = X 
AND Seats.Number = Y;

You might find depending on what data access and UI technology that you're using that working with a surrogate primary key is easier, and just add a unique constraint on the Area, Row, and Number combination.

2
  • Yes, I see. That would indeed be much better and way more simple. Thank you. Though I must ask: Unique for 3 columns, that means that the combination of the three is unique, not each one individually ?
    – Kalec
    Apr 14, 2013 at 10:28
  • 3
    @Kalec Yes, the combination is unique. If they were unique individually, you could have no more than one seat per area! Apr 14, 2013 at 11:10
6

Why don't you just have an Areas Table and add the ID as a FK to the main table? This way you wouldn't need to write complex queries and if another Area was added, then you only need to add a record to the Areas table, and not a new table.

CREATE TABLE #seats
    (
      Id INT ,
      SeatRow INT ,
      SeatNumber INT ,
      AreaId INT
    )

CREATE TABLE #areas ( Id INT, AreaName VARCHAR(50))

This kind of idea, and you can force referential integrity where you can only add an Area if it exists in the Areas table http://en.wikipedia.org/wiki/Referential_integrity

4

It's really worth thinking about the types of query you are going to want to run over your data, and the structure of the rest of your database - that will almost certainly help you think through the model.

For instance, you're likely to want to link your seats to tickets when you sell a seat for a performance. That suggests you have a table called "performance", and another table called "ticket". Consider the structure of "ticket" - you want to link it to the seat. In your current design, the foreign key from "ticket" to "seat" is ugly - you'd need four columns to store this, and you can't guarantee that the ticket wouldn't have links to two seats if there's a bug somewhere.

That suggests that the proposals from devdigital and christiandev are probably more useful.

You're also likely to want to find out how many free seats you have for a performance - again, your current design would need to query 4 tables, whereas the alternative just requires a single query.

Finally, if you ever want to change the way you structure "seats" - for instance by adding a "default price" column, your current design needs you to make that change in 4 places; using a single table with an "area" foreign key allows you to make that change just once.

1
  • Thank you for your answer. I agree with you wholeheartedly. My main problem was that I had no idea how to make the table work because I wanted row + seat + area to be unique identifier (I saw it only as a primary key) but area had to repeat. Now, if I knew that I can make the combination of the three unique, I wouldn't even asked the question, but because I am so new at this, simple answer just don't "pop in".
    – Kalec
    Apr 14, 2013 at 11:53
3

It will be better to keep only one table with an area column

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.