Database design: Dividing multiple identical tables, good or bad?

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.

-
You'd be better off having a single table with the area as another column. –  Phil Apr 14 '13 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 '13 at 10:34
Use a unique constraint –  Phil Apr 14 '13 at 10:36

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

-
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 '13 at 11:56
High quality and well thought out answer. Wish more folks responded at this quality. –  DeepSpace101 Apr 14 '13 at 18:52
Thank you, Sid. I hope you know that I'm going to quote you on my CV. –  Mike Sherrill 'Cat Recall' Apr 14 '13 at 19:11
Very nice answer :) –  christiandev Apr 14 '13 at 20:32
@Mike Sherrill 'Catcall', out of interest, with this design, if an additional area was added 'E', they would need to midify the seats table to accommodate? So, this soution is best if we know for certain the area would not change, but 2 tables if the areas need to be flexible? –  christiandev Apr 15 '13 at 16:01

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.

-
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 '13 at 10:28
@Kalec Yes, the combination is unique. If they were unique individually, you could have no more than one seat per area! –  Branko Dimitrijevic Apr 14 '13 at 11:10

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

-

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.

-
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 '13 at 11:53

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

-