Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2010
    Posts
    5

    Question Unanswered: Relationship Question

    Hey, I'm designing a simple SQL database for a class project, and I'm really stuck on how to represent this one relationship in the database. I'm making a party planner application, and there are several types of cakes, so I have a cake table that lists the cakes price, size, etc. Then I have an event table, and each event has a certain number of each type of cake. I don't have a primary/foreign key relationship or anything, but they are related. I simply have the cake table to reference the price and size of each type of cake when needed. Each event includes ALL of the types of cakes, so I dont need to map cakes to events or anything. I want to query the cakes table as being related to the events but i see no such many to many, one to many, or one to one relationship by primary/foreign keys. How am I supposed to represent this as a relationship, since all my tables should be related in the database?

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    How do you represent the cake(s) for the event? are you putting them as a concatenated value, multiple rows? I would think that you would have an events table that just lists your event, then you would have subordinate table(s) to denote what is needed for the event. Maybe a single table with a type col to denote if the subordinate item is a cake, a band or whatever.
    Dave

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    hold on. 1 event can have many cakes and 1 type of cake can be at many events. How is this not many to many again. Pass-through table.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    <ot>I'm always a little disappointed when I open threads like this and find the question is about database design </ot>
    Quote Originally Posted by SixSence View Post
    Each event includes ALL of the types of cakes
    O Rly? Could you elaborate? For example, if you had a wedding cake in the cake table and a funeral in the event table, the recently widowed Mrs Flump gets a three tiered white fruit cake, want it or not?
    Am I understanding you correctly?

  5. #5
    Join Date
    Apr 2010
    Posts
    5
    Sorry for the confusion, let me elaborate. It's a really simple project I'm doing for class. I'm making a 'Party Planner' application, but the only thing it really manages is the number of cakes needed for the event and how much the event costs.

    There are six types of cakes. Each has a specific size and flavor. Every single event will include a certain number of each type of cake. This is because the application keeps track of the type of pieces of cake that every person in the event wants, so there will be different kinds of cake and different sizes of the same cake to fit the number of people who want pieces.

    So, every event includes ALL of the cakes. That's why I don't need an explicit foreign key many-to-many relationship since I don't need to pick out which cakes belong to which events, because they all do. The cakes table simply gives me a reference for the price, size, flavor, etc of each type of cake.

    I don't know how to link the two tables together, it's like they are separate entities since I don't have a key in each table to link together. I'm not very good with databases, I only know some basics of the 3 types of relationships, so I figure I'm missing something simple.

    Also, the reason I need them related is so when I map the tables to objects in my program, it will be included as an association in the object model, so I can reference each cake from each event object.
    Last edited by SixSence; 04-27-10 at 17:04.

  6. #6
    Join Date
    Apr 2010
    Posts
    5
    Does nothing like this ever come up? You have an object that every entity needs a reference to, and that object is stored in a separate table with its own properties, and there is no key linking the two tables together, because it is a given that every entity has that object. Am I supposed to unnecessarily complicate the tables so they are linked by a many-to-many relationship?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SixSence View Post
    ...the application keeps track of the type of pieces of cake that every person in the event wants
    here's the key piece of information we did not have before

    cakes ~are~ related to events -- indirectly, via people

    you need a three-way person-event-cake many-to-many relationship table

    the PK of that table will be (person_id,event_id,cake_id), and there may even be a data attribute in that table, qty (indicating how many pieces of that particular cake at that particular event the person wants)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2010
    Location
    Charlotte, NC
    Posts
    11
    r937 nailed it! What you are looking to design is often referred to as a mapping table. It's a table that does nothing more than relate one or more tables in a m to n relationship. When you said an event can have more than one cake, and a cake can be at more than one event, you're describing a m to n relationship.

    I would suggest following r937's advice and creating a mapping table as suggested, then join events to that table, then from the map to cakes, and you'll have the results you're looking for.
    ---
    Shannon Lowder
    Database Engineer
    http://toyboxcreations.net

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Shannon Lowder View Post
    r937 nailed it!
    <Grudgingly>OK Rudy, that's ONE so far this year.</Grudgingly>
    But just you wait, I will catch up to you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    I will catch up to you.
    in problems solved? by next week, probably

    in number of occurrences of the exact phrase "[person] nailed it"?

    you're on

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The attached datamodel is not ideal, but should be enough. (You could elaborate it with a CakeFlavour reference table (referenced from Party_Cake_Order), a People Table (referenced from Partygoer), ...)

    Party_Partygoer captures those people who go to a certain party.
    Party_Cake_Order captures the number of cakes those partygoers want at the specific Party(s) they went to.

    I used BIGINTs to store the amount of slices partygoers want, just to be on the safe side
    Attached Thumbnails Attached Thumbnails PartyCake.jpg  
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Giving the guy a little guidance is one thing, doing his homework for him is another.

  13. #13
    Join Date
    Apr 2010
    Posts
    5
    woah woah woah guys... hold on. This program isn't in depth enough to do all of that. I have all of the tables i need. I don't need a person table. The program does not collect any information for a person. Each person is the same, they are just a number to designate how many people are at the event. I was told a "lookup" table is what I needed, that contains reference information that does not change often. Is this a good alternative? There is no mapping needed, and I have all the tables I need, I just need a way to relate the event to the cakes without FK relationships.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks like i didn't nailed it after all

    thanks for the update, but let me just confirm that you don't keep track of which person gets which cake?

    so basically you have an event, and you somehow get a count of how many people at that event want cake 1, and how many people want cake 2, and so on? just total counts?

    i don't think i like what you said here, though --
    I just need a way to relate the event to the cakes without FK relationships
    why did you not want FK relationships?

    anyhow, it appears that you have two tables -- cakes and events

    the way to "relate" them is with a relationship table

    CREATE TABLE eventcakes
    ( event_id
    , cake_id
    , pieces
    );

    this tells you for each event and each type of cake, how many pieces are needed

    is that closer to what you're looking for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Apr 2010
    Posts
    5
    For all intensive purposes just assume I'm not keeping track of each individual person. I prompt the user for how many of the total people attending the event want a piece of each type of cake, so I'm just keeping track of totals.

    Now, I have a 'NumCakes' table, and in that table I have a column for each type of cake that has the number of cakes needed for each event. For example, I have event 1, then I have a row in the NumCakes table for event 1, a column for cake TypeA that tells me how many cakes of Type A I need for event 1, then a column for Type B, and so on. This table has a FK relationship with the events table (one-to-one).

    This works great, but I need certain details about each type of cake, such as size and price, to help me calculate the cost of the event. That's why I created the Cakes table so I can simply lookup the price and size of each type of cake when I need to.

    Assume I have six types of cakes. If I create a relationship table like you suggest, I have to add six rows to the relationship table for each event, which seems unnecessary. The way I have it, I simply have to add one row to the 'NumCakes' table for each event, then lookup the price from the Cakes table.

    With that said, your solution seems to be the best if I want to have a relationship between the events table and cakes table.

    Thanks for the replies everyone! I have a lot of new approaches to use in the future now

Posting Permissions

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