Do I create a new table or keep adding onto existing table?

[MMock]

As some of you may remember from previous recent posts of mine, I am converting a VB6 application to .NET/C#. The DB is sql server.

Because sometimes the current application needs immediate enhancements, the engineer who wrote the code in the first place is doing these. Then I have to catch up.

There is a table called Jobs which keeps track of jobs we do for customers. It has two columns called DealerForecast and MRForecast and these are bits. Of 73,000 Jobs records, the bits are on 3x.

What the recent maintenance was, was to add a forecastdate and forecast amount to the Jobs table for each forecast type. So now there are six columns total having to do with forecasts.

I think I want to *strongly suggest* that we create a new Forecast table. It will have a key back to Jobs. It will have a Type column to say whether MR or Dealer. It will have a Date and an Amount. Then, Jobs won't need these 6 columns that are so seldom used. It doesn't need the on/off bit in the first place because the presence or absence of a Forecast record will determine if there is one or not. Is this a good idea?

In general, how do you determine whether to add onto an existing record or create a new table? When there's a 1:many relationship, obviously yes. But 1:1, does it matter as much? I just hate to see these six columns around all the time when the times they actually have data in them is so minimal.

Thanks.

[MMock]

Oh, and I forgot about Notes! It looks like they want to also add a Notes field for each forecast. This is not there yet, but in my .NET version I am adding a memo field to the form for them to type free text. This would be an ntext field in sql server. And who knows what they will want to add on next month or next year. So shouldn't this be its own table?

[FunkyDexter]

In general, how do you determine whether to add onto an existing record or create a new table? When there's a 1:many relationship, obviously yes. But 1:1, does it matter as much?

1:many - always. 1:1 - never (unless it's for performance reasons, i.e. hiving blob data out to a separate table).

But your scenario is neither of these. Your scenario is a 1:0 to 1. In other words, for a Job, sometimes there will be a forecast and sometimes there won't. A job has either 0 or 1 forecasts. This is a form of 1:many so, yes, it really ought to be given it's own table (in fact, ultra strict normalisation uses this technique to completely eliminate nulls from the database - it's called Boyce-Codd normal form).

However, you need to do a little more than that because you have an additional constraint: there can never be more than 1 forecast for a job. The way to enforce that is actually quite easy. You make JobID the primary key of the Forecasts table and it acts as both the primary key of Forecasts and the Foreign Key back to the Jobs table. This ensures that a given job id can only exist once in the Forecasts table.

It will have a Type column to say whether MR or Dealer

Can a job have both types of forecast or is it an "either or" situation? If a job can have both then you should have separate tables for each type of forecast. If a job can only have one of them then a single table with a type column is better because this enforces the rule that it can have only one forecast in total.

[MMock]

Your scenario is a 1:0 to 1. ... This is a form of 1:many so, yes, it really ought to be given it's own table.

Yes, that is what I had decided.

you have an additional constraint: there can never be more than 1 forecast for a job. ...make JobID the primary key of the Forecasts table and it acts as both the primary key of Forecasts and the Foreign Key back to the Jobs table.

OK, thanks for that tip.

If a job can have both then you should have separate tables for each type of forecast.

OK, I would not have known to make two tables. I will change that now. Thanks.

[techgnome]

Can a job have both types of forecast or is it an "either or" situation? If a job can have both then you should have separate tables for each type of forecast. If a job can only have one of them then a single table with a type column is better because this enforces the rule that it can have only one forecast in total.

Maybe... if the tables are virtually the same for both types, it may be worth putting them into ONE table, with an additional "Type" column that indicates which they are. With just two types, it's not a huge deal... but if you add more types, and thusly more tables... it can make reporting a nightmare down the road. This is just simply how I'd go about it, but then I don't know if the data in the two tables overlap in anyway. If the data is completely different or has very little in common, then two tables is the way to go. But if we're talking about (jsut for argument sakes) 10 fields, where 7-8 of them are common between the two types, it's worth exploring in my opinion.
You can still create a unique constraint that includes the PKey and the Type fields to ensure that you have only one of each.

-tg

[MMock]

I know, that's what i was thinking at first. The data is exactly the same, so I had a type column. But, I just changed all my code to two tables. I don't think we'll ever have any other kinds of forecasts though, so maybe not a big deal. Thanks for the alternative.

[FunkyDexter]

I think that one's open for debate. I would argue that the fact they can have one or both implies that the forecasts are behaving as separate entities and would keep the tables separate on that basis. But you're right, the unique constraint could be satisfied by putting a unique constraint across both JobID and Type columns (in fact, I would make them both part of a compound PK if I were going down that route.