0

2 quick questions:

I have done my best to learn about normalization forms, but wasn't sure about this specific situation.

  1. I have an Event.
  2. An Event has many Instances.
  3. An Instance has: Food, People, Cost, Evaluation, Location, etc.

Should food, people, cost, etc. each get their own table or simply be columns in the Instance table (BLOBs)? I have no reason to think they would need a full table each, since they belong only to an Instance, and would not be shared. However...I don't know if the future may give me reason for them to be their own, so is it better to just treat them as their own?

Secondly, IF all of those should each be their own table, is it useful to also store the Event foreign key as well? In case I want to call every Food list for an event regardless of instance, or would I just get all the instances from an event and then call the info for each instance. If I expect that call to happen, is that enough cause to add the key or is this poor planning?

Improve this question

2 Answers 2

Reset to default
2

Might as well plan for the future here and now before you get down the road and regret your decision later. It would probably be better to store your Instance Items (Food, People, Cost, etc) in separate tables. Instance -> Instance Item is a many to many relationship, you'll need some linking tables in between the object tables to allow this many-to-many relationship.

Event | EventID Instance | InstanceID, EventID InstanceFood | InstanceID, FoodID InstancePerson | InstanceID, PersonID ... Food | FoodID, FoodName Person | PersonID, PersonName

In the case of the linking tables, the primaryKey is the combination of both the InstanceID and the other id value. With this setup, there is no need to store event IDs in your object (food, person) tables. If you wanted to figure out which events a particular person is associated with, just join Person with Instance Person and Instance to get all associated EventIDs.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks! I think you are right about the tables, but I just wasn't sure. However, I probably would still have it be one to many, only because even if someone does use all the info from an instance item, I want it to be a new item (prevent overwriting). Is that a reasonable strategy?
@Jdewzy If a single entity of type person or type food will only be associated with a single entity of type instance, than you can safely drop the linking tables as well. Just add the InstanceID to the Food, Person tables as well. It seems reasonable to me, but I'm hesitant to say whether this is a good idea or not though because I am not fully aware of the entire problem as you are.
1

Your model is underspecified. What does it mean that an Event "has"a Cost? Is it a single grand total expressed in Cents? That belongs into a numeric COST column. Is it an itemization of several chargeable products or services? Then it has to go into a separate table, because that is the only sane way of implementing 1:n relationships. Likewise, is the Evaluation a single score on a scale from 1-10? Column. Is it a structured questionnaire with several (or even, God forbid, a variable number of) Q&A positions ? Table. Is only the number of people important? Column. Is the identity of the associated people important? Table.

As for 2., if you have associated tables that hold 1:n data there is really no way around letting them refer to the "owning" record via a foreign key. For instance, if you do need a table to model the 1:n relation between an Instance and People, then People must have an InstanceId column.

Improve this answer

1 Comment

Thanks. I am still working on the answer to the first question. Initially it was simple but it is getting more complex (how it usually goes?) and so the tables may also just be a good idea for that reason. Regarding the 2nd question, it was about whether it should have the EventId, not just the Instance Id. I didn't know if it would be easier than for comparing all the Event's People (regardless of instance), or if it's just as fast to search for the instances of an event and then each People associated with that instance.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.