identifying or non-identifying relationships

[enrico.c]

Hi everybody!

I'm developing an application to manage personal investments.

Business rules are:

1. each investor can have 0, 1 or several portfolios (with portfolio i mean a group of investments) and each portfolio MUST belong to one and only one investor

2. each portfolio can have 0, 1 or several investments and each investment MUST belong to one and only one portfolio

3. an investment (for example an investment fund) can have 0, 1 or several events (events are every kind of money movement regarding the investment) and each event MUST belong to one and only one investment

4. for simplicity (is not a professional application...) i assume that portfolios never change the investor they belong to and investments never change the portfolio they belong to.

I made two design model.

Model 1. Uses identifying relationships for investor-portfolio and portfolio-investment relations.

http://img10.imageshack.us/img10/3721/model1ident.jpg


Model 2. Uses non-identifying non-optional relationships for the same relations, and also uses surrogate PKs for Portfolio and Investments entities.

http://img12.imageshack.us/img12/931...l2nonident.jpg


Which one do you think is better?

What can be the advantages and disadvantages for each one?

Probably model 1 is more correct, but has multicolumn PKs...

Any comment, criticism and suggestion are very welcome.

Thank you for your time!


Enrico