CODEWITHSUNDEEP
database-design
cskwrd
cskwrd

Reputation: 2885

database relations

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!

Upvotes: 0

Views: 121

Answers (6)

loginx
loginx

Reputation: 1112

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)

Upvotes: 2

Pierre
Pierre

Reputation: 35246

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 )

Upvotes: 0

Aaron
Aaron

Reputation: 7098

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.

Upvotes: 0

littlegreen
littlegreen

Reputation: 7420

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).

Upvotes: 1

rayd09
rayd09

Reputation: 1897

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.

Upvotes: 1

Randy Minder
Randy Minder

Reputation: 48402

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.

Upvotes: 3

Related Questions