Jump to content

Guidelines for when to make a new table?


nycpost
 Share

This topic is 2308 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

This topic is 2308 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.