I have a busted five table design for a food item guest ticket . Here are the names(Actual) of those tables: GuestTicketType, GuestTicket, GuestTicketOrderCategory, GuestTicketOrder & GuestTicketPayment.
The incomplete(busted) design looks like this: guesttickettype has a field named guesttype, which is one of three guest types: dinein, takeout, delivery.
The guestticket table holds the personal info like the first and last name on the ticket and the relevant data. The guestticketordercategory holds an ordercategory field that uses 7 different food item options: pizza, subs, salads, beverages, desserts, sides and addons. My first inclination was to set the two pre-populated tables (guesttickettype and guestticketordercategory) to hang off the guestticket table and the guestticketorder table, respectively. Not sure if that works or not....
I don't know how to set the tables properly, however, i do know the end game. Each guest should have an order and a payment to each ticket. Does that make sense?
My question, then, is what is the best way to accomplish that, either with the present setup or some other variation based on your recommendation?
Thank you for much needed help!
From what I can make from your description and table names, here are the fields I would use:
GuestTicket: ID, GuestTicketType (FK), Name, etc
GuestTicketOrder: ID, GuestTicket (FK), GuestTicketOrderCategory (FK)
GuestTicketPayment: ID, GuestTicket (FK), etc
Please note that this allows each ticket to have several Orders and several Payments. Another design that would work is the following:
GuestTicket: ID, GuestTicketType (FK), GuestTicketOrder (FK), GuestTicketPayment (FK), Name, etc
GuestTicketOrder: ID, GuestTicketOrderCategory (FK)
GuestTicketPayment: ID, etc
Please note that the second design allows each ticket to have only one order and only one payment. The design really comes down to what type of relationship you need between ticket, order and payment and the direction of the relationship. The most general case would be a many-to-many relationship, which I did not list.
Thank you, timothy. I fixed the design based on your advice. I'm hoping to complete the forms and populate the db using aspx across the internet.
Thanks again for your helpful post
I am just curious: which method did you end up using? Each of the methods I mentioned supports different requirements, i.e. each ticket only has one order, each ticket can have several orders, etc.
I used the first version. I apologize for not stating the entire specifics of my needs. I should have said this:
Each guest ticket can have more than one order item and of course more than one payment. A guest ticket that only supports a single order wouldn't clearly depict the actual ticket. The ticket would be fragmented using the second option and incoherent, would it not?
May I ask you if you thought the table names were coherent and clearly suited for the intended purpose? Did I leave anything out that should have been inlcuded?
Thanks again for your help.
Since each ticket needs to support more than one order as well as more than one payment, then the first option is the option you should use.
As far as the names of the tables, I think they worked well.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.