Guidelines for when to make a new table?

[nycpost]

Hi Everyone, 

 

I’ve been building a database over the last several years and recently discovered that I am missing a few entities in it’s structure. The database is designed to help organize post-production for film and television. It was only as I got further along down the road that I realized there were elements I was missing. I wanted to do certain things and discovered that with the way it’s currently built, I couldn’t. I’m going back now to re-think the structure of the database, adding in new tables to give me the functionality I need (and may need in the future). Going back to basics has brought up a couple questions:

 

1) What criteria do you use to determine if something should go in it’s own table or be represented by multiple values in a field? For example, for a particular show we will have several major cuts that we do during post: the editor’s cut, the director’s cut, the producer’s cut, etc. Each of those “cuts” can have several versions. I could organize this a couple different ways: in a single table called Cut Versions with two fields Cut Name and Cut Date:

 

CUT VERSIONS

Cut Name - Cut Date

Editor’s Cut - 1/25/16

Director’s Cut - 1/27/16

Director’s Cut - 1/29/16

Producer’s Cut - 2/5/16

Producer’s Cut - 2/10/16

Producer’s Cut - 2/13/16

 

…Or I could organize this with two tables: Cuts and Cut Versions (and create a relationship between the two):

 

CUTS

Cut Name

Editor’s Cut

Director’s Cut

Producer’s Cut

 

CUT VERSIONS

Cut Name - Cut Date

Editor’s Cut - 1/25/16

Director’s Cut - 1/27/16

Director’s Cut - 1/29/16

Producer’s Cut - 2/5/16

Producer’s Cut - 2/10/16

Producer’s Cut - 2/13/16

 

Organizing into this parent and child table structure allows me to view the cut version information from a more macro level. I can compare running times, etc. This seems to give me a greater degree of flexibility, and, as I understand it, is more proper in terms of database logic, but…

 

How granular should one get? Is there ever a point where breaking things apart into separate tables could become a problem? What are the fundamental guidelines for determining if something needs to live in it’s own table? My sense tells me: anytime I have a field’s value duplicated across several records (E.g. “Cut Name” above), it's a sign that I should consider making a new table to house that field. 

 

2) How often do developers like yourselves find that you need to augment the structure of a database years after you’ve started it? Would you say that structural modifications are normal? I did months of work early-on developing my entities, their relationships, and thinking long and hard about the philosophy behind these ideas and trying to figure out what I needed. Finding out I’ve missed something has been a bit of a kick in the pants. 

 

Thanks,

 

Mike

[comment]

1 hour ago, nycpost said:

My sense tells me: anytime I have a field’s value duplicated across several records (E.g. “Cut Name” above), it's a sign that I should consider making a new table to house that field. 

Yes, that is a sign. But it doesn't always mean that you do need another table. This is where your business rules come into play: is the "missing" entity something your solution needs to track? For example, my Contacts table may have many contacts in the same city - but if my business is not real estate, I will consider it to be a mere coincidence.

In your example, where someone is likely to ask: "how many different cuts exist for this show?" I think it's clear that such table would be beneficiary. But the relationship would be based on a meaningless CutID, not on the name.

--
P.S. Please use the default font when posting.

 

Edited February 28, 2016 by comment

[Lee Smith]

I edited the font apparently when you were typing up your post.

[nycpost]

Thanks, Comment. That makes sense. I have a couple other instances where I have things like this that I've been wondering about breaking off into their own tables. I'll look at them and see if those entities are things that I need to track now - or have a chance of becoming things I need to track in the future. And yes, the relationship would definitely be by IDs, not by names. 

[Fitch]

The FileMaker Training Series discusses this topic at some length.