Hello all,

I am working on a database to store mutual fund data, and one table in particular to keep distributions for the fund.

The table already exists and was designed by my predecessor, and I think so far it is a good design. I will try to explain as best I can...

Columns:
id (pk, auto increment),
fundId (identifies to related fund)
dtAsOf (date of the distribution)
[3 other relevant date fields],
nValue (amount of the distribution)


Technically, I don't think I need id as fundId/dtAsOf could be a unique key, but it already exists and I generally do like to have the "id" field for tables. I have a data feed from a warehouse that populates this table, and comes in monthly. I have just learned of a requirement to have a 2nd feed come in yearly, and give a breakdown of the nValue. I think I have two correct options here, and one incorrect option, but I would like some validation on my thought process here.
One important note is that the sum of the breakdown does not equal the nValue, it's only a partial breakdown.

1) I would like to just add nValueBreakdown1 & nValueBreakdown2 columns to the original table, and when I find the fundId and dtAsOf in the yearly source, update the row with the breakdowns.
2) Add a second table with an id (pk), table1_id (fk), nValueBreakdown1 and nValueBreakdown2. This seems like overkill, but it would at least still be a good design, right?
3) Add a second table which is basically a duplicate of the 1st with the two additional columns. The reason for this is the data source is separate from the first table, even though the data is the same. To me, that is not an acceptable reason to add a new table with duplicate data.

What are your thoughts on these approaches? Thanks for sharing. Please ask any questions if anything was unclear.

Surely you meant to say "is a" and not "could be"?

I would create a second table that holds a foreign key to the distribution, the type of breakdown, and the breakdown amount. Your primary key would, presumably, consist of the distribution and the type of breakdown. The table would hold one row for each breakdown you want to record about a distribution.

Thanks for the response. That is a better way to do option 2... Full disclosure, I was originally pulling for option 1 while a colleague wants to go with option 3, and I came up with option 2 as a middle ground, pretty hastily, as I was putting together this question. With your suggestion I would also be able to add more breakdowns than just the 2, should that possibility arise. I think this is probably the best way to go.

Would you agree that your proposed option is the most flexible and best designed, option 1 is acceptable but not optimal, and option 3 is just not a good design? Sorry to lead you into an answer there, but I guess I'm looking for some comments around all the possibilities. =)

I'm obviously biased towards my recommendation, otherwise I wouldn't have recommended it.

None of your options appear normalized to me, so I'd stay away from them. On the other hand, without a detailed understanding of your requirements and the data you're attempting to model, I can't say for certain.