CODEWITHSUNDEEP
databasedatabase-design
soultrust
soultrust

Reputation: 599

Should I create another pivot table or just add another field on current pivot table that will be NULL for most records?

I'm building a database of films. PROJECTS and MEMBERS are a many-to-many relationship with a ROLES pivot table connecting them. This ROLES table also has a field called "role_type" which stores an integer corresponding to a role (actor, director, editor, cinematographer, etc). But just for actors (which will be more than 90% of the records), I need to also store the name (string) of the character played. Would it be poor design to simply add another column on the ROLES table called "character_name" even though it will be NULL for most of the records?

I was also thinking about creating another pivot table in between PROJECTS/MEMBERS that would store the character names, but I felt that was redundant because I'm already connecting PROJECTS/MEMBERS through ROLES. It also seems to be overkill to add another table just to store a character name (and I don't intend on storing any more information about the character).

Please enlighten this newbie. Any suggestions?

Upvotes: 0

Views: 44

Answers (1)

mwahnish
mwahnish

Reputation: 131

I think you will be better off with a separate Character table, but belonging to Role and not a join table. Roles that are Actors would have this association, but the others would not.

This will allow you to be more flexible in the future and avoid null fields that will never be filled. Read here for more opinions on null: https://dba.stackexchange.com/q/5222

You may not have plans now, but you could end up needing to categorize the character as "Lead", "Supporting", or "Background". A separate table will make this easier in the future.

Upvotes: 0

Related Questions