Two tables or one table ?

Question

Hi, a quick question in regards to table design..

Let's say I am designing a loan application database. As it is right now, I will have 2 tables..

Applicant (ApplicantID, FirstName , LastName, SSN, Email... ) and

Co-Applicant(CoApplicantID, FirstName, LastName , SSN, Email.., ApplicantID)

Should I consider having just one table because all the fields are the same.. ??

Person( PersonID, FirstName, LastName , SSN, Email...,ParentID (This determines if it is a co-applicant))

What are the pros and cons of these two approaches ?

Answer 1

If the Domain Model determines that both people are applicants and that are related, then they belong in the same table with a self-referential foriegn key.

Answer 2

How about since each Applicant can have a Co-Applicant -- just go with one table in total. So you'd have Applicants, which has an optional foreign key field 'coapplicant' (or similar).

Answer 3

If the fields between applicant and co-applicant are identical, then I would suggest that you put them in the same table and use an "applicant type" field to indicate main or co- applicant. IF there's some information special about the co-applicant (such as relationship to main applicant, extra phone numbers, other stuff) you might want to normalize to a separate table and refer from there back to the co-applicant (by (co-)applicant ID) in the applicant table.

Answer 4

You may want to read up on database normalization.

I think you should have two tables, but not those two. Have a table "loans" which has foreign keys to an applicants table, or just have records in applicants reference the same table.

Answer 5

The advantages: - Makes searching easier: If you only have a phone number or a name, you can still search, in a single table and find the corresponding person regardless of he/she being a co-applicant or a main-applicant. Otherwise you'd need to use a UNION construct. (Yet, when you know that you search for a particular type of applicant, you add a filter on the type and you only get such applicants. - Generally easier to maintain. Say tomorrow you need to add the tweeter id of the applicant ;-), only one place to change. - Also allows inputing persons with say an "open/undefined" type, and assign then as applicant or otherwise, at a later date. - Allows to introduce new types of applicants (say a co-latteral warrantor... whatever)...

The disadvantages:

• with really huge (multi-million person records), there could be a slight performance gain with a two table approach (depending on index and various other things
• SQL queries can be bit more complicated, for example with two separate joins to the the person table, one for the applicant the other for the co-applicant . (Nothing intractable but a bit more complexity.

On the whole, the proper design is in most likelihood the one with a single table. Only possible exception is if over time the info kept for one type of applicant was starting to diverge significantly from the other type(s) of applicant. (And even then we can deal with this situation in different ways, including the introduction of a related table for these extra fields, as it may make more sense; Yes, a two table system again, but one where the extra fields may fit "naturally" together in term of their semantics, usage etc...)

Answer 6

I suggest the following data model:

PERSON table

• PERSON_ID, pk

LOAN_APPLICATIONS table

• APPLICATION_ID, pk

APPLICANT_TYPE_CODE table

• APPLICANT_TYPE_CODE, pk
• APPLICANT_TYPE_CODE_DESCRIPTION

LOAN_APPLICANTS table

• APPLICATION_ID, pk, fk
• PERSON_ID, pk, fk
• APPLICANT_TYPE_CODE, fk

Person( PersonID, FirstName, LastName , SSN, Email...,ParentID (This determines if it is a co-applicant))

That works if a person will only ever exist in your system as either an applicant or a co-applicant. A person could be a co-applicant to numerous loans and/or an applicant themselves - you don't want to be re-entering their details every time.

This is the benefit of how & why things are normalized. Based on the business rules & inherent reality of usage, the tables are setup so stop redundant data being stored. This is for the following reasons:

1. Redundant data is a waste of space & resources to support & maintain
2. The action of duplicating the data means it can also be different in subtle ways - capitalizations, spaces, etc that can all lead to complications to isolate real data
3. Data incorrectly stored due to oversight when creating the data model
4. Foresight & Flexibility. Currently there isn't any option other than applicant or co-applicant for an APPLICANT_TYPE_CODE value - it could be a stored without using another table & foreign key. But this setup allows support to add different applicant codes in the future, as needed - without any harm to the data model.

There's no performance benefit when you risk bad data. What you would make, will be eaten by the hacks you have to perform to get things to work.

Answer 7

Both of your variants have one disadvantage: any person can be an applicant and co-applicant twice and more. So you should use table Person( PersonID, FirstName, LastName , SSN, Email... ) and table Co-Applicants (PersonID as Applicant, PersonID as CoApplicant)

Answer 8

Keep Two table> 1ST User type code ID In this table u can keep user type ie applicat And Co applicant 2nd table User--> here u can keep all the field with similar coloums with user type code as foregin key. By this you can easily distingush between two user.

Answer 9

I know - I'm too late on this.... The Loan Application is your primary entity. You will have one or more applicants for the loan. Drop the idea of Person - you're creating something that you can't control. I've been there, done that and got the T-Shirt.