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.)
Solution 1:
Make the event_id nullable.
ALTER TABLE events
MODIFY COLUMN event_id int unsigned NULL ;
This will allow you to insert rows which reference nothing (NULL):
INSERT INTO events
(user_id, event_id, title)
VALUES
(a_valid_user_id, NULL, 'test event 1') ;
And to see events without a "parent" event:
SELECT *
FROM events
WHERE event_id IS NULL ;
Test at SQL-Fiddle-1
(Parenthesis)
Unless you have some braindead ORM that requires all tables to have a column named id, it's better to have names that describe what they represent. I'd rename id to event_id and event_id to parent_event_id:
CREATE TABLE events (
event_id int(10) unsigned NOT NULL AUTO_INCREMENT,
user_id int(10) unsigned NOT NULL,
parent_event_id int(10) unsigned NULL,
title varchar(200) NOT NULL,
PRIMARY KEY (event_id),
--- ...
) ;
Best Answer
When designing a new database, it's important to know before hand how it will be used. Of course, it's difficult to have perfect knowledge of exactly how your database will be used before hand, because you may be prototyping, your business needs may change over time, etc.
I've found that a good rule of thumb for designing a relational database for a transaction processing system is to assume that each tangible thing your system tracks gets its own table.
Consider the following ERD for your situation:
In your case you have
VENUEs that each have one or moreSTAGEs. Each stage has manySEATs. However, note that you want to keep track ofPRICEs for seats, so for the sake of sanity in maintaining pricing data, your seats are arranged intoSECTIONs. The price is set at the section level instead of at the individual seat level. The price also depends on the show time (EVENT) because you might have different prices for different days of the week or different times of day (week day matinee is cheaper than Saturday evening, for example).You have multiple show times, but there is probably going to be many things common to an event with multiple show times, so you want an
EVENTtable and a childSHOWtable. This lets you keep show and event (show time) information in third normal form (3NF) which is always a good place to start with a transactional system database.Assuming that you also want to keep track of who is actually in each seat, you need to have
TICKETs andCUSTOMERs in your database as well.To address your specific question about how to track seats, as rows or as columns, I strongly suggest you track seats as rows. Relational database management systems like MySQL are built to easily handle rows of data. If you handle seats as columns, you would probably need a different seat table for each stage, since the seats will be different for each one. That would make your queries very awkward and complex. If you are going to use a relational database for your data, then use it the relational way and put things in rows and facts about things in columns. Your code will be simpler and your life will be better.