Posted by Gordon Burditt on 09/20/05 21:10

>I'm looking at storing snippets of details in MySQL about what credit cards
>a business excepts. Rather than have a whole column for Visa, another for
>Amex etc ... I am looking at having a column called payment types and
>inserting multiple codes ... e.g. ViAmBcCa

To me it would seem appropriate to have a table with the primary key
of the business id, a column for payment type, and if there is more
than one payment type accepted, you put in multiple rows.

>Is this a good way of doing things? To me it'd be a lot cleaner and limit
>amount of Db work to be done.

Awfully strange definition of 'clean' there. Sorta like "the toxic
waste hides the mud and kills the Anthrax".

The amount of work the db has to do with WHERE payment_types like '%Vi%'
(it pretty much has to scan the whole table if there are no
other qualifiers) is a lot more than WHERE b.businessid = p.businessid
AND payment_type = 'Visa'.

Your method might make sense if all you EVER want to do with payment
types is DISPLAY it and never SELECT on it. My bet is that you do
want to select on it, or will eventually.

>Is this a sensible way in your opinion? What's
>the best way of getting the info back out via PHP? Put in a delimiter e.g.
>Vi-Am-Bc-Ca- or keep codes to two digits and split by that length?

Put the info in multiple rows. In some situations, group_concat()
might be useful.

If you must use the delimiter, go all the way and put it first and
last also. payment_type LIKE '%-Vi-%' won't match Vi if Vi is *first*
or *last* unless you store it as -Vi-Am-Bc-Ca- . On the other hand,
you're in trouble without a delimiter if you store 'ViAmBcCa' and
try to match on a payment type called 'iA'. You might be able to
still save this approach with a case-sensitive search, but it's still
very clunky.

Gordon L. Burditt

[Back to original message]