database relations

Question

Hey everyone,

Let's say I want to make a database that relates emotions with activities. Would it make sense to make a new table every time i encounter a new emotion and inside that table would by say the ids of all the activities that relate to that emotion each id being its own record, or is there a better way of doing it?

Thanks everyone!

Answer 1

I would think you would want a table named Emotions which would contain the universe of emotions. Then you would want a table named Activities which contains the universe of activities.

You then need to determine if each emotion could have multiple activities, and if each activity could have multiple emotions. If so, this is what's called a many to many association, and you would want a third table that contains each emotion that is associated with each activity. This third table is often called a many to many association table, or mapping table.

Answer 2

I wouldn't recommend adding a new table for every emotion. It would probably be better to have an Activity table, an Emotion table, a join table between Activity and Emotion which I'll call ActivityEmotion.

Answer 3

It would be better to have one table called emotions (id, name), another called activities (id, name) and a third one relating the two. (id_emotion, id_activity).

Answer 4

It is generally not a good idea to create a new table for each of a set of entities. Setup your tables like this:

Emotions -Id -Name -...other information

Activities -Id -Name -... other information

ActivityEmotions -EmotionId -ActivityId

This way, ActivityEmotions contains all of the Emotion-Activity relationships.

Answer 5

don't create a new table. I would use 3 tables:

• one for the activties
• one for the emotions
• and a table activity_to_relation (many to many association )

Answer 6

This related to the subject of Database Normalization. What you want is set up a schema that looks like this:

Table 'activities': (id, name)
Table 'emotions': (id, name)
Table 'activity2emotion: (emotion_id FK to emotions.id, activity_id FK to activities.id)