Sql-server – Database design: Dividing multiple identical tables, good or bad

database-designsql server

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.

Best Answer

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.)

Related Question