Posted by Gordon Burditt on 09/21/05 04:24

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

Incidentally, what ARE Bc and Ca? Bank debit card and cash?
Bank of British Columbia and State of California Food Stamp cards?

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

Um, *WHAT TYPE OF* overhead? Are you concerned about disk space here?

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

Please state what it is you wish to optimize:

(1) Minimum number of database tables
(2) Minimum disk space occupied by the database
(3) Minimum disk I/O used in updating the database
(4) Minimum CPU used in updating the database
(5) Minimum disk I/O used in querying the database
(6) Minimum CPU used in querying the database
(7) Minimum number of database rows
(8) Minimum number of database columns
(9) Minimum coding time for MySQL queries
(10) Minimum coding time for PHP
(11) Minimum disk space for PHP scripts
(12) Minimum (4) plus (6) (You have to state an assumed ratio of
updates to queries).
(13) Minimum (3) plus (5) (You have to state an assumed ratio of
updates to queries).
(14) Minimum MySQL server network traffic
(15) Minimum PHP/Web server network traffic
(16) Maximum contract payments to the programmer (you)
(Job Security Programming)
(17) Something else (state it!)?

You may choose only one primary optimization. If you want more
than one, chances are I can name an optimization that will improve
one at the expense of the other. For example, for choices (2),
(3), (4), and (9), don't use any indexes (a generally stupid choice
if the tables are large enough to worry about disk space filling a
floppy). For (1), you can, I think, put everything in one table,
by adding a column for the table name, and include columns in that
table for every column in the tables you're replacing. This is
also a horrible choice by most normal criteria.


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

Writing code in a way to lock out expected future changes is not
generally a good idea, unless you're trying Job Security Programming.

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

If you are doing SEARCHES by LIKE '%Am%' or LIKE '%Ca%', they'll
still work. If you split the field apart by length into an array
and look for appropriate array elements, PHP will like it fine,
regardless of order.

>That's the code answer I want!

Decide what you want to optimize. You've stated a number of them,
and you can't have them all.

Gordon L. Burditt

[Back to original message]