Many tables or a big table with views

[nairdeepthi2000]

Hi ,
I have some data of members of a credit society, like address details,nominee details,loan(if taken) details etc,now i m confused whether put all these data in same table with a ID as PK and create views to access different data (eg address) or create different tables like address details , nominee details and so with the member ID as PK , which would be better and why
pleez help me out

[iburyak]

You have to look at your data and combine in groups of data when one thing can refer to multiple things.

Example:
One person can have only one personal information available like address, phone…
This person could possibly have multiple loans and there is no point to repeat address information for each loan.
In this case you create following tables:
[PHP]Personal_Table
PersonID, FirstName, LastName, Age, Address, Phone and so on

Loan_Table
LoanID, PersonID, loand data.[/PHP]

This way you will not have to repeat all personal information for each loan but easily can join to Person_Table using PersonID column and retrieve it if necessary.

Hope it helps.

[nairdeepthi2000]

hi dear friend

thank u so much for ur response it was really helpful

In my case a person can take a loan only f he has refunded his previous loan so no question of multiple loans arises , and in each type of details there is only a single record for each Member_ID eg nominee details there are 2 nominees for each member so i have taken t as nominee1 , nominee2 for each Member_ID and at the end of each month a transaction report is prepared and in that too there is a record for each Member_ID.

So shall i club all details like nominee,persona l, transaction etc in 1 table and make Member_ID as its PK?

I have used the PK FK ,Unique constraints , if u can tell me of any other constraints i should take care of it would be really helpful

[nairdeepthi2000]

hi

If i dont club the data n 1 table and create different tables for each like address details, nominee details , transaction details etc with Member_ID as PK will t giv rise to redundant data with the same Member_ID repeating?

Kindly reply as soon as possible, it would be a great help

[pkreemer]

It sounds like you really want one table! :-) And if you're not interested in learning a few database concepts, then you might be better off with one table. Certainly you can hack things together and it can seem more concrete and logical. Everything is in one place.

To break it into separate tables, you'd need different queries to join your information back together into reports. It's not usually hard but it's one more thing to learn.

One question: do you want to record history of member name changes, address changes, loan details, and so on? Maybe you're accomplishing this by storing the old report files.


Oh, and you asked about redundant data. No you don't end up with member data duplicated if you go with a multiple table design. The opposite is more likely to be true: In any larger database project you can definitely end up with duplicated data if you go with a single table!

[nairdeepthi2000]

Hi ,

Thank u friend for ur response

Yes I m recording history of member name changes, address changes, loan details by storing the old report files.

And can u pleez tell me how can I end up with duplicated data if I go with a single table

It sounds like you really want one table! :-) And if you're not interested in learning a few database concepts, then you might be better off with one table. Certainly you can hack things together and it can seem more concrete and logical. Everything is in one place.

To break it into separate tables, you'd need different queries to join your information back together into reports. It's not usually hard but it's one more thing to learn.

One question: do you want to record history of member name changes, address changes, loan details, and so on? Maybe you're accomplishing this by storing the old report files.


Oh, and you asked about redundant data. No you don't end up with member data duplicated if you go with a multiple table design. The opposite is more likely to be true: In any larger database project you can definitely end up with duplicated data if you go with a single table!

[nairdeepthi2000]

Hi,

If learning joins is the thing to make it into seperate tables , I have used joins in my tables like a table transcPerMember joining transaction details and Member details.

But i am worried about the redundancy.
Can u pleez tell me , If i go with multiple tables i will have same Mem_ID repeating in all of them then how wont i end up with redundancy.

It sounds like you really want one table! :-) And if you're not interested in learning a few database concepts, then you might be better off with one table. Certainly you can hack things together and it can seem more concrete and logical. Everything is in one place.

To break it into separate tables, you'd need different queries to join your information back together into reports. It's not usually hard but it's one more thing to learn.

One question: do you want to record history of member name changes, address changes, loan details, and so on? Maybe you're accomplishing this by storing the old report files.


Oh, and you asked about redundant data. No you don't end up with member data duplicated if you go with a multiple table design. The opposite is more likely to be true: In any larger database project you can definitely end up with duplicated data if you go with a single table!

[pkreemer]

Well you can have a number of tables using a Mem_ID field as a foreign key, but they'd all refer to a single Members table. The Mem_ID field itself is an integer and you wouldn't worry about the size of your foreign key fields. Having a logical, clean design and consistent data should be more of a focus.

Is that what you're asking about duplication of data? Maybe you can explain your concern more, as I don't think I'm quite understanding your question.