Theater Seat Storage in DB

I wasnt sure as to how to exctly store the seats of a reservation booking system.So far i have been thinking that ill be needing these tables.Also i was going to do this as a webpage.

• Show
  • Id
  • Theather_Id
  • Name
  • AgeRating
• Theather
  • Id
  • Name
• Show_Time
  • Id
  • Show_Id
  • Time
• Show_Day
  • show_time_id
  • day
• Seat_Category
  • Id
  • Name
• Seat_Status
  • Id
  • Name
• Reservation
  • id
  • rowno
  • seatno
  • username (nullable)
  • seatstatus_id
  • show_timeid
  • Date
• Users
• Membership
• Show_Seat_Price
  • show_id
  • seat_category_id
  • start_row
  • start_no
  • end_row
  • end_s开发者_开发知识库eat
  • price

My biggest problems are how to store the seat array in the db also i wish to make a seat categorization such as bronze,silver,gold which each certain section will have an allocated pricing.

Empty layout:

FRONT

0 0 1 1 1 0 0

0 1 1 1 1 1 0

1 1 1 1 1 1 1

1 1 1 1 1 1 1

BACK

But since i require the categories of gold , silver and bronze this further complicates the matter

---

You should store each possible seat as an individual record in the database, arrays really aren't standard practice for storage in DBs; they must break a normal form or two but I don't remember which one ;)

Each site would have a rownumber, columnnumber etc, and a unique ID. That way you can get history for each seat, store additional properties for each seat, etc. Much better! If a seat is broken, you can disable it with a flag etc - if only certain seats are suitable for disabled people (near the aisle for example), you can flag it as such too, and query on how many 'accessible' seats are left. In fact, definitely much better :)

To populate, you'd need to write a stored procedure / initialisation utility.

---

Since you already have a numerical array of the seats, how about adjusting that array so it looks like this?

FRONT
0 0 3 3 3 0 0
0 3 3 3 3 3 0
1 2 2 2 2 2 1
1 1 1 1 1 1 1
BACK

...where 1, 2 and 3 represent bronze, silver and gold. (I've used the same example you gave, and guessed which seats would be which category)

Hope that helps.