identifying or non-identifying relationships

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

identifying or non-identifying relationships

Author

Topic

enrico.c
Starting Member

2 Posts

Posted - 2009-03-17 : 20:59:30

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/9316/model2nonident.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

Have a nice life!

enrico.c
Starting Member

2 Posts

Posted - 2009-03-19 : 04:11:14

No suggestions?....so i deduce that models are both perfect?

Please, give your opinion.

Have a nice life!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources