Database design for fields with multiple selections 1

[03Explorer]

I am designing a database that has some fields that more than one option could be selected. What is a proper method for this type of data to be collected? example below:

Possible Payment Methods: ( )Credit Card, ( )Personal Check, ( )Cash, ( )Debit Card, ( )Money Order, ( )Travelers Check

If the field is called PAYMENT_METHOD, how do I capture the data to that field if these would be check boxes?

 

[LNBruno]

You'd have to create a 1-to-many table which would store the primary key from the main record to the (potentially) many different payment methods selected.

< M!ke >
I am not a hamster and life is not a wheel.

 

[03Explorer]

I was so focused on having all information in that field, rather than a relationship table.

Thanks

 

[mmerlinn]

If you don't want to use a table, then you could compress the information from the check boxes into a single field. However, you would need to extract it every time you needed it and recompress it if it changed. That can be very time consuming in a large database.

As far as how you would put it all in a field, it really doesn't matter as long as it works AND it is EASILY MAINTAINABLE by your successors.

Some ways you could do it assuming Cash & Money orders are true in above example:

1) "Credit Card = No,Personal Check = No,Cash = Yes,Debit Card = No,Money Order = Yes,Travelers Check = No" (A simple extraction with all info in record)

2) "Cash,Money Order " (Assumes missing items are false, but requires missing info be handled by extra coding)

3) "NNYNYN" (Only states of boxes are stored. Names must be handled in your coding.)

There are many other ways also that will work fine so long as the code is EASILY MAINTAINABLE.




mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Olaf Doschke]

I'd say you normally choose one payment method, but if it would be like that:

To store something like "NNYNYN" is dangerous, if payment methods change, eg one deleted, one added. You can't map which N or Y belongs to which option after a while.

You then need to take the precaution, that payment methods can never be deleted but only deactivated, you can store such binary data about the choices, you could even use bitwise ORing the choices and could then store it in an integer field, which then would be sufficient for 32 different payment methods.

In the normal case of one choice to store a foreign key to a payment methods table is of course much simpler.

Bye, Olaf.

 

[mmerlinn]

Olaf:

You have brought up some interesting points on why storing this info in a single field is generally not a good idea. Basically, you have emphasized my point that the information and code MUST be EASILY MAINTAINABLE. Most times this precludes storing multiple items in a single field.

If the info is stored in any form where the names of the info are divorced from the values of the info, one runs the real risk of mixing them up without ever being able to map the the values back to the correct names. This is especially true if the values are stored in some binary, e.g. NNYNYN or 001010, format.

The only time I store multiple info in a single field is when the contents of the field, and the underlying code, are not likely to ever change. Multiple info in single fields is just too hard to maintain if it changes even occasionally.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Thargy]

03,

have you considered the possibility of someone making payment by more than one method. I was once paying by credit card, when the transaction took me to my credit limit and was declined. I tried again, with just enough to reach the card limit (but not exceed it) and made up the rest of the payment with cash.

Is there a business requirement for this? Has it been considered? Either way, keep all of the possible payment types in a separate table, so that you can query for payment types easily. Looking for an abstruse abbreviated notation is SQL is just plain daft.

Regards

T

Grinding away at things Oracular

 

[03Explorer]

thargtheslayer,

I actually used payments as an example. I am building a database from a form that someone can select many options (around 40) and more than one option will be checked. I am trying to find a good solution for data collection and reporting on those items.

So far I have something like this:

tblMain
ID
OptionsID


tblOptions
ID
Item1 (y/n)
Item2 (y/n)
Item3 (y/n)
...
Item39 (y/n)
Item40 (y/n)

The relationship being one to many from tblMain to tblOptions.

Anything better as an solution?

 

[lespaul]

Anything better as an solution?

yes.....a more normalized solution!

going back to your example of payments. Let's say that you have 'Purchase' table that stores the amount of the purchase and then you have a payment table. You would NOT store the information in the payment table like this:
[tt]
tblPurchase
PurchaseID(PK) PurchaseDate TotalTransaction
15 7/3/2007 250.21


tblPayments
PaymentID(PK) PurchaseID(FK) CashAmt CreditAmt MO_Amt
27 15 100.00 50.21 0.00
[/tt]

With the setup above you would have to add each field to get the total payment. Additionally, anytime a new payment option is added you will have to change the table structure and add that field to EVERY query in your database.

If you normalized, your payments table would be set up like this:
[tt]
tblPayments
PaymentID PurchaseID PaymentType Amount
27 15 CASH 100.00
28 15 CREDIT 50.21
[/tt]

now with the setup above you can add infinite number of payment types and not have to change your table structure or queries.

Check out the Fundamentals document below for more on normalization.

Hope this helps!





Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Thargy]

03,

I think I'm beginning to see what you're trying to do. This sort of looks like an Access database with some user-friendly forms for data gathering. This is not an uncommon task, but nonetheless, does require some careful thought.

The biggest thing to understand is that your requirements drive the db design, and then the forms are merely ways of elegantly populating the designed tables. The forms follow the database, not the other way around.

Design the database in a normalised manner, against the user's requirements. If none exist, get the user to write some. If the user is you, write down your own requirements as this will assist you in thinking clearly about the design. You immediately gain a baseline requirement against which to work. If you subsequently find change is necessary, then alter the requirements document to show this.

Once the database is done, craft the forms to suit. Then test end-to-end (or "soup to nuts" if you're American) against the requirements.

All the previous posts about normalising etc. are sound advice IMHO.

Regards

Tharg

Grinding away at things Oracular

 

[chrissie1]

Or try this

thread183-1372891



Christiaan Baes
Belgium

"My old site" - Me

 

[Olaf Doschke]

lespaul,

I think 03 was thinking of a payment method table like this:

paymentmethods
ID
methodname
active

payment
ID
amount
...

Now ou of course need a n:m relationship table

methodsforpayments
paymentID
paymentMethodID

restrict deleting of used payment methods. If you don't want to offer a method/option, you set it's active flag to false. (Historical) data integrity is given, but options can be added or deactivated.

The idea of using bitwise logic is of course nice, if you can guarantee the relations will never be broken.

Bye, Olaf.



Bye, Olaf.

 

[mwatts98]

I think that Lespaul is on the right track with the normalized table. I have a scenario that extends on this dicussion. I have a form that fronts a database and needs to store the payment info like above. However, I also need to store data like the money order number, amount of the money order, credit card number, expiration date, cVV2 code, authorization number, billing address etc. There could be one or more payment types with each order and more than one payment of each type. Example: I could receive two money orders and two credit card payments to make the total dollar amount of the single person's order.

Does anyone know how the best approach would be for the above scenario? I have thought of having a table for money orders and a table for credit card payments.

Any and all help is appreciated.

Michael