Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012

    Database design question for newbie

    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
    Date date
    Time INT
    Venue VARCHAR(20)
    Title VARCHAR(40)
    Map ??

    Table 2: Map
    Seat# VARCHAR(2)
    Seat Area VARCHAR(10)
    Status INT
    X-coord FLOAT
    Y-coord FLOAT

    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!

  2. #2
    Join Date
    Feb 2012
    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?

  3. #3
    Join Date
    Mar 2012
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts