Question Question concerning DataSet Relationships

[daveofgv]

Hello Everyone.

I have a question concerning my database relations and don't know if I am doing it correctly. I am hoping that someone will be able look at it and let me know if anything looks wrong.

About my program (small type of loan office) - we have clients that take out a certain type of loans. These clients have cosigners most of the time, but sometimes they might not. It is not unusual for a client to take out 5 or 6 loans at one time. So I am thinking that the relation has to be client one to many with poweratty. However, if the client needs a cosigner than I need to relate the client and cosigner and poweratty together :/ I think the below setup will be good if the client needs a cosigner, however, if the client does not than this is greater than my expertise.

What I have now is below. What I am seeing is that if a client does not need a cosigner than I will not be able to enter data for the poweratty because the primary key of cosigner is null.....
Can someone look and let me know if I am doing this correct? (hope what I typed is easier to understand)



I appreciate any help on this and look forward to some advise.

Thanks in advanced.

daveofgv

 

[jmcilhinney]

The first thing you should do is work out your naming conventions. Decide whether you want your table names to be singular or plural and stick with that decision. At the moment you're mixing and matching and that is bad. Inconsistency is always bad because someone looking at it may wonder if there's a reason for the difference. Personally, I always use singular names for my tables but I know that some people prefer plural names.

Also, while what you do is up to you, I recommend getting rid of that pointless "tbl" prefix on your table names. Are you really going to forget that they're tables? I can understand a prefix on views, to distinguish that minority from the majority tables, but a prefix on tables serves no useful purpose.

As for your relationships, surely the very point of the database is to track loans so why is there not a Loan table? I would have a Client table and a Cosignatory table, then a Loan table with ClientId and CosignatoryId foreign key columns, with that last column being nullable. If every loan requires a power of attorney then you might consider putting that information in the loan table if the same information won't be linked to multiple loans, especially if there is very little other specific loan information. Otherwise, you would have a separate PowerOfAttorney table and a PowerOfAttorneyId foreign key in the Loan table.

 

[daveofgv]

Hi jmcilhinney -

Thank you for the details. I will surely start doing what you mentioned. As for the relationships - we call the loans power of attorneys. these are not standard loans like you see in a bank. This office is small, but can be busy. The database diagram above is a an example and not the full schema. However, for the loan portion the above image will represent what tables are needed, Client, PowerAtty, CoSigner.

Since I am asking on a forum - I understand that any answer will be in the repliers opinion and not given as how it should be setup, however, I would like to only ask for directions. I assume that since a client can have multiple loans (poweratty) than the relationship should be Client (1) Poweratty(many). However, if not all Powerattys require a Cosigner, but a Cosigner can cosign many loans at once, Would it be right to have Cosigner (1) and Poweratty (many)?

Also, if the above is correct - would there be any relationships between Client and Cosigner in your opinion?

I understand it's all about how I want the relationships setup, however, multiple database tables kind of mess me up when it's in regards to relationships. I would think this should be easy, but most of the videos and examples I see on the internet are all about customers and orders and I can't see how that references my case.

Thanks in advanced and I apologize if I am asking too much.

daveofgv

 

[jmcilhinney]

I assume that since a client can have multiple loans (poweratty) than the relationship should be Client (1) Poweratty(many).

Yes.

However, if not all Powerattys require a Cosigner, but a Cosigner can cosign many loans at once, Would it be right to have Cosigner (1) and Poweratty (many)?

That would be 0..1-to-many, so it would be like a 1-to-many but nullable on the many side.

Also, if the above is correct - would there be any relationships between Client and Cosigner in your opinion?

No.

 

[daveofgv]

Thank you :beer: