Database design for fields with multiple selections
Database design for fields with multiple selections
(OP)
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?
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?
RE: Database design for fields with multiple selections
< M!ke >
I am not a hamster and life is not a wheel.
RE: Database design for fields with multiple selections
Thanks
RE: Database design for fields with multiple selections
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!"
RE: Database design for fields with multiple selections
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.
RE: Database design for fields with multiple selections
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!"
RE: Database design for fields with multiple selections
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
RE: Database design for fields with multiple selections
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?
RE: Database design for fields with multiple selections
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:
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
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:
tblPayments
PaymentID PurchaseID PaymentType Amount
27 15 CASH 100.00
28 15 CREDIT 50.21
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
RE: Database design for fields with multiple selections
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
RE: Database design for fields with multiple selections
thread183-1372891: Speed Up Performance And Slash Your Table Size By 90% By Using Bitwise
Christiaan Baes
Belgium
"My old site" - Me
RE: Database design for fields with multiple selections
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.
RE: Database design for fields with multiple selections
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