Posted by elyob on 09/21/05 02:29

"Gordon Burditt" <gordonb.kfpw5@burditt.org> wrote in message
news:11j0k4b4e1vg0ee@corp.supernews.com...
> >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.

I've always hated the idea of 1 & 0's for Visa, Amex etc .. I see the sense,
but it's a lot of overhead.

>>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'.

Exactly! I want to keep down the number of tables I have. I also want to now
keep my tables & rows to a minimum. I want to keep my Db to a minimum. I
also know that PHP takes a lot of effort.

I like the idea of smoothing MySQL, I believe less columns, more data, less
calls.

It's a toughy. But it has to be less effort on the server, the better.

I reckon bad PHP is as bad as bad MySQL.

>
> 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.

Good point. However I can rewrite that database in the future.

>>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.

I don't like delimiting unless by fixed coded. Still, what happens if I go
AmCa instead of CaAm by mistake? PHP won't like that!

That's the code answer I want!

Thanks

[Back to original message]