Hi All, I'm trying to design a database that will be implemented as a SQL db back-end for a Java GUI app. The project is for a theater ticket sales s/w product. Part of the project requires an image of the seating layout for a venue that the user can click on to select seats for sale. This information has to be stored in a database for retrieval at later times. The database will also be able to store information for multiple performance at different dates/times, each with their own map of seats for sale.
Here is my concept for two tables to store this information:
Table1: Performance Event Instance
Table 2: Map
Seat Area VARCHAR(10)
My question is how do I tie the map with multiple columns to a row in the performance table? I don't see how I could do a table join with the UID from performance because one table describes one seat layout map while one table will describe many performance events. It's almost like I'd have to do a nested table of some sort.
I'm investigating possibly using a BLOB where I serialize a Java object and store it in the database and just contain all of the information in the BLOB. This would work nicely with my Performance Event Table because each BLOB would fit directly into the Map cell for each row. However, I don't have any experience with serialization so thought their might be an easier solution.
Thanks for any help you can give to a database newb!
If you add a foreign key to the Map table which references the primary key of the Performance table, you can use that as part of the primary key of Map.
If you pack your data into a BLOB, you'll be unable to query over that data, the whole thing will have to be loaded and deserialized each time you want to check it, and you'll have to iterate over the whole table in many cases. If you mean to throw away the good parts of the dbms like data checking and constraints, joins and ad-hoc querying, query optimization, and more, why even use a database?
ahhh Foreign Key. After researching the foreign key concept that makes alot of sense, thanks! I was sure their was something like that but I wasn't sure what it was called or how to implement it. Told you I was a database newb.