Relationship Question

[SixSence]

Hey, I'm doing this project for class, 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?

[olivthill2]

(Not easy to read a compact paragraph)

What are your tables? T_Cake, and T_Event, and that's all?

I think you would need also T_Type_of_Cake, and T_Event_Cake, although you are reluctant to create that latter table.

[SixSence]

Sorry for the long paragraph. I feel like theres no reason for a table to map the relationship between the two tables, because all of the cakes are in each event, not just some of them, so why would I need a table to tell me which cakes are in which events and vice versa?

The cake table exists only to tell me the properties of each type of cake, which the user can change, therefore I need to store the properties in a database. It's simply a reference table for each event.

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. I'm not very good with databases, I only know some basics about the three types of relationships, so I must be missing something simple.

Thanks for the help!

[dglienna]

That would be a LOOK-UP table. Every table should have a unique key, though. It speeds up everything

[olivthill2]

Ok, each event has every kind of cake.

But events don't have the same quantity of cakes, since you wrote "each event has a certain number of each type of cake". That quantity must be stored somewhere.

The best place is in a table linking events and cakes, one I would call T_event_cake, and which would contain the following fields: event_id, cake_type_id, cake_qty. The key would be composed of the first two fields, or you can have a special id for that table if you prefer.