Normailization of periodic data

Hi everyone,

I have/had a situation very similar to another recent thread, “Table Setup Help...”, but I read the thread and had a go at normalizing my analogous table. My old table, Fund, looked like this:
(AssetClassId is a foreign key for another relationship.)

I didn’t like it much, but I thought it was the only way to go. The other thread helped me a lot and I came-up with two tables, Fund and Price, instead:
Do you agree that this is normalized and is the correct way to go? Like the other thread mentions, if I have, say, fifty funds, I will have to add fifty new records to the Price table every month or quarter (or however frequently I decide to keep data), but hopefully a well-made form can make that pretty easy. (The long-term goal is to have it downloaded automatically with VBA, but I have no idea how to do that yet,)

I have attached screenshots of the tables as an alternative to my ASCII art . I wanted to share this and also get opinions on whether this is good or not.

Thank you!
Ryan


PS
Should I add a post to the other thread linking to this one if this one works-out?

your new structure looks properly normalised. dont worry about the number of rows. 50 a month is nothing to access

I recommend that you do not use ID as the primary key name. Give your primary keys proper descriptive names so that you can easily see where foreign keys point. So the pK for Fund should be FundID and the pk for Price should be PriceID.

There is another issue and that is that you need a unique index on the Price table that is a compound key comprised of FundID and PriceDate which I am assuming is effective date so I would name it EffectiveDate. The purpose of the compound unique index is to prevent a duplication of the combination of FundId and EffectiveDate. You also have a choice with this table. Unless the Price table has a "child" table, I would eliminate the autonumber PK and use the FundID and EffectiveDate as the compound primary key but there are those who would disagree with me on that.

Thank you for all your input! I chose the short Id name for the primary key fields to avoid redundancy within their own table, but have renamed them like FundId when they are used as foreign keys. I did that with the consideration that any SQL I need to write would be cleaner, like Fund.Id instead of Fund.FundId, but I understand what you mean. I don’t have a lot of database experience—do you think this an important issue or not that big of a deal?

I am not familiar with indexes. Reading just now, it is my understanding that they are used for faster searching and sorting, right? Or do you mean to make the combination of the FundId and PriceDate* the key (if the plain Id field is removed)? That makes sense to me; good catch!

Also, when a primary key made of foreign keys is used (like described above), and you suggest to remove the plain Id [I see here that the naming is getting a little tricky!], do you mean that some people would disagree with you on that because the other view is to always keep an Id field whether it’s really necessary or not?

* I guess I’m not totally set on the PriceDate name, but I chose it because it’s like if you check a price of a stock or fund, and it’s $100 that day, then the date of that price.

no, theres no problem per se, with having keys all called ID - its just that if EVERY table has a field called "id", it may be confusing to you to work out which particular table you are dealing with

its just a field name, but if it is slightly more descriptive it can be useful - it doesnt help that access automatically tries to add an autonumber primary key called ID - the best thing is to let Access add the key, but then change the keyname to something else

to use your tables you have a table

where fundid in the funds table, is the same as fundref in the fundprices table (you could call it fundid in the fundprices table, which helps fior some functions, but can be confusing in others - i am sure some users may deliberately name matching keys idenitically - its a matter of taste)

also in this case its a matter of taste whether you have fundpriceid as a field at all, since you COULD set the primary key to be FundRef AND pricedate together. - i tend to have an autonumber field in almost EVERY table - it does no harm and may be useful

finally, note that we try to avoid just calling fields DATE or NAME - they are legal but they are also reserved words in Access, and again may cause ambiguity/issues down the line.

I found a good page to check reserved words for several different database types:
(I try to check it if I think of it in case I ever need to move out of Access.)

About the fundprice table (using your names), I like the idea of keeping an autonumber field whether it seems important or not. But in this instance where fundref and pricedate both make-up the primary key, it would be correct/necessary to make the autonumbered fundpriceid not part of the key, right? Because that would defeat the purpose of using the other two fields together?

gemma's suggestion is fine. I prefer using the same name as both pk and fk but using consistant suffixes works as well. I just don't like a bunch of tables with the same name as their pk.

I spoke with the Access development team about the "ID" issue and their response made sense. When Access assigns the autonumber PK, it does so before the table is named. Plus, Access wouldn't necessarily be able to determine what part of the table name you wanted to use as the pk name.