Looking for table stucture advice 1

[G12Consult]

Hi am looking for some advice on best practice for the following scenario.

I have a table which contains a list of financial assets - Credit Card, Home Loan, Owned Property

When I user selects a financial asset at the front end, I want to record further information regarding the product they selected, however each of these products will have different information required.

e.g. Credit Card selected
Get user to provide Bank Name, Credit Limit

Owned Property selected
Get user to provide Address Details, Suburb, Postcode

So from the above, different types of information?

How would I record this in a table without having to have a table for each asset.

One way I was thinking was to maybe have one table but have fields like Info 1, Info 2, Info 3, Info 4 and use the application at the frontend to populate specific fields depending on what financial asset was selected.

 

[Olaf Doschke]

Why avoid further tables? You're thinking too much about a convenience of your code to not need to deal with different tables, but you don't get around it as the information you need to collect is individual and needs individual forms, too. Why should the backend quench this individual information into same type of fields? When you do your forms, do you also always use one text input field and ask users to provide data of multiple informations into a comma separated list of values? Or do you have multiple inputs? Think about that.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[G12Consult]

The reason I wanted avoid further tables was it could become unmanageable if more and more financial assets are added.

And yes the frontend webforms will be specific for each asset type.

 

[Andrzejek]

How about something like:
I am sure there are better ways to do it, but it is a start....

FinancialAssets table[pre]
ID Asset
1 Credit Card
2 Home Loan
3 Owned Property
[/pre]
SomeInfo table[pre]
ID AssetID TypeOfInfo
1 1 Bank Name
2 1 Credir Limit
3 3 Address Details
4 3 Suburb
5 3 Postcode
[/pre]
Final table:[pre]
ID Client InfoID SomeData
1 123 1 First National Bank
2 123 2 1 000 000
3 125 3 123 Main Street
4 125 5 90210 [/pre]

Tables can easily grow, no limits, no empty (NULL) fields

"One way I was thinking was to maybe have one table but have fields like Info 1, Info 2, Info 3, Info 4 and use the application at the frontend to populate specific fields depending on what financial asset was selected. "

Bad, bad idea. You will end up with a lot of records with most of the fields empty.


---- Andy

There is a great need for a sarcasm font.

 

[G12Consult]

I like that approach. It would work for me I reckon. Will give it a try. Thank you

 

[Andrzejek]

You may also consider adding a
Status table:
[pre]
ID Status
1 New
2 Active[blue]
3 No longer offered[/blue][/pre]

FinancialAssets table[pre]
ID StatusID Asset
1 2 Credit Card
2 2 Home Loan
3 2 Owned Property[blue]
4 3 UFO Insurance[/blue][/pre]

You may had [blue]UFO Insurance[/blue] for a couple of years, but this service is no longer offered.
Just a suggestion...


---- Andy

There is a great need for a sarcasm font.