PHP, MySQL and snippets of info ...

[elyob]

Hi,

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

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

Suggestions appreciated!

Nick

[Gordon Burditt]

Re: PHP, MySQL and snippets of info ...

>I'm looking at storing snippets of details in MySQL about what credit cards[color=blue]
>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[/color]

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.
[color=blue]
>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.[/color]

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.
[color=blue]
>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?[/color]

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

[Malcolm Dew-Jones]

Re: PHP, MySQL and snippets of info ...

elyob (newsprofile@gm ail.com) wrote:
: Hi,

: 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

: Is this a good way of doing things?


No. The "correct" way would be a table that lists businesses and the
cards they accept.

create table business_accept s_cards
(
business_id
card_id
)

Other methods might occasionally be best, but I can't think of a good
example off hand.

E.g. to make a form to accept payment, you'll end up with something like

echo <select name = customer-card-type>

$sql = select card_id from business_accept s_cards
where business_id = $this_business_ id

while ($row = fetch row)
{
echo <option value= $row[card_id]> $row[card_id] </option>
}
echo </select>

I.e. the code will almost always be straight forward.


$0.10

--

This programmer available for rent.

[elyob]

Re: PHP, MySQL and snippets of info ...


"Gordon Burditt" <gordonb.kfpw5@ burditt.org> wrote in message
news:11j0k4b4e1 vg0ee@corp.supe rnews.com...[color=blue][color=green]
> >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[/color]
>
> 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.[/color]

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.
[color=blue][color=green]
>>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.[/color]
>
> 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'.[/color]

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.
[color=blue]
>
> 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.[/color]

Good point. However I can rewrite that database in the future.
[color=blue][color=green]
>>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?[/color]
>
> 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.[/color]

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

[ManChild]

Re: PHP, MySQL and snippets of info ...

[color=blue]
> 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.
>[/color]

As an experienced DBA I can tell you that forgoing indexing (leading
wild cards will force this) will have a significantly bigger performance
hit in the long run than coding multiple short queries.

Even if you do not do the query with a join, querying on explicit
numeric, indexed values will result in a miniscule query time for the
applicable cards -- you can't thumb your noise at normalization and
expect to get good results unless this is going to be a small database.

Why does it have to be a choice between bad PHP and by database
structure? Dont start off knowingly making bad choices :D Trust me -
it never pays!

J

[Gordon Burditt]

Re: PHP, MySQL and snippets of info ...

>> >I'm looking at storing snippets of details in MySQL about what credit[color=blue][color=green][color=darkred]
>> >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[/color][/color][/color]

Incidentally, what ARE Bc and Ca? Bank debit card and cash?
Bank of British Columbia and State of California Food Stamp cards?
[color=blue][color=green]
>> 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.[/color]
>
>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.[/color]

Um, *WHAT TYPE OF* overhead? Are you concerned about disk space here?
[color=blue][color=green][color=darkred]
>>>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.[/color]
>>
>> 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'.[/color]
>
>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.[/color]

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.

[color=blue]
>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.
>[color=green]
>>
>> 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.[/color]
>
>Good point. However I can rewrite that database in the future.[/color]

Writing code in a way to lock out expected future changes is not
generally a good idea, unless you're trying Job Security Programming.
[color=blue]
>[color=green][color=darkred]
>>>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?[/color]
>>
>> 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.[/color]
>
>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![/color]

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.
[color=blue]
>That's the code answer I want![/color]

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

Gordon L. Burditt

[elyob]

Re: PHP, MySQL and snippets of info ...


"Gordon Burditt" <gordonb.g6ok5@ burditt.org> wrote in message
news:11j1dj24k5 nbn37@corp.supe rnews.com...[color=blue]
> Incidentally, what ARE Bc and Ca? Bank debit card and cash?
> Bank of British Columbia and State of California Food Stamp cards?
>[/color]

These will be internal codes that mean something to me and will be
documented.
I was thinking Barclaycard, Visa, American Express, Cash, but haven't
actually got that far yet.
[color=blue]
> Um, *WHAT TYPE OF* overhead? Are you concerned about disk space here?[/color]

Not disk space, but creating multiple rows will require more work from the
server. Personally I like the idea of keeping similar stuff in a single row
in the same master table rather than create multiple rows in a seperate
table. I'm looking at this as a technique for other stuff too, e.g. hotel
facilities.

One of my data suppliers for hotels has over 100 columns in one of their
feeds supplting details such as "internet 0", "minibar 1", "satellite tv 1"
.... personally one column aggregating all this stuff would be ideal. Maybe
overly complex at first, but saves a lot of extra work when populating and
reading that table.
So, rather than expand my table with 6 extra columns, I just add one column
with called "payment_ty pe".
[color=blue]
> (3) Minimum disk I/O used in updating the database
> (5) Minimum disk I/O used in querying the database
> (7) Minimum number of database rows
> (8) Minimum number of database columns
> (9) Minimum coding time for MySQL queries
> (13) Minimum (3) plus (5) (You have to state an assumed ratio of
> updates to queries).[/color]
[color=blue]
> 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.[/color]

It's less about optimisation and more about tidy, concise tables. I just
don't feel right about creating either 100 columns for every snippet .. e.g.
for restaurants "kids menu", "vegetarian menu", "vegan menu", blah blah blah
....
[color=blue]
> 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.
>[/color]

I think I'd best test it out next and see how horrible a technique it is!

[elyob]

Re: PHP, MySQL and snippets of info ...


"ManChild" <starritt@gmail .com> wrote in message
news:xy1Ye.7912 7$3S5.75439@tor nado.rdc-kc.rr.com...[color=blue]
>[color=green]
>> 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.
>>[/color]
>
> As an experienced DBA I can tell you that forgoing indexing (leading wild
> cards will force this) will have a significantly bigger performance hit in
> the long run than coding multiple short queries.[/color]

This is an interesting point. I have a database that people search by
retaurant name. I do a search "LIKE '*name*'". I know this isn't the best,
but quite a few businesses start with the word "The". e.g. I might search
for "Ritz", when I really want "The Ritz".

There's only 30k rows, but I want to build for expenetial growth.
[color=blue]
>
> Even if you do not do the query with a join, querying on explicit numeric,
> indexed values will result in a miniscule query time for the applicable
> cards -- you can't thumb your noise at normalization and expect to get
> good results unless this is going to be a small database.
>
> Why does it have to be a choice between bad PHP and by database structure?
> Dont start off knowingly making bad choices :D Trust me - it never pays![/color]

I just think one column into an array would be tidier in the schema and let
PHP do all the work. If I have multiple columns, it'll be just as much work
for PHP and more for MySQL.

[Jerry Stuckle]

Re: PHP, MySQL and snippets of info ...

elyob wrote:[color=blue]
>
>
> Not disk space, but creating multiple rows will require more work from the
> server. Personally I like the idea of keeping similar stuff in a single row
> in the same master table rather than create multiple rows in a seperate
> table. I'm looking at this as a technique for other stuff too, e.g. hotel
> facilities.
>[/color]

And keeping everything in one row will require more work from the
server. Every request for a row will have to get more data, for
instance. This will require more disk space, more memory and
potentially more processing time. Additionally - it becomes much more
complex to add a new item.
[color=blue]
> One of my data suppliers for hotels has over 100 columns in one of their
> feeds supplting details such as "internet 0", "minibar 1", "satellite tv 1"
> ... personally one column aggregating all this stuff would be ideal. Maybe
> overly complex at first, but saves a lot of extra work when populating and
> reading that table.[/color]

Hmmm, sounds like a perfect example for a linked table. Aggregating the
data IS more complex - both at first and later. And again - what
happens if they add a new feature - like a sauna - to a room?
[color=blue]
> So, rather than expand my table with 6 extra columns, I just add one column
> with called "payment_ty pe".
>
>[color=green]
>>(3) Minimum disk I/O used in updating the database
>>(5) Minimum disk I/O used in querying the database
>>(7) Minimum number of database rows
>>(8) Minimum number of database columns
>>(9) Minimum coding time for MySQL queries
>>(13) Minimum (3) plus (5) (You have to state an assumed ratio of
>> updates to queries).[/color]
>[/color]

Maximum processing time encoding/decoding the column, maximum code
changes when adding a new payment type...
[color=blue]
>
>
> It's less about optimisation and more about tidy, concise tables. I just
> don't feel right about creating either 100 columns for every snippet .. e.g.
> for restaurants "kids menu", "vegetarian menu", "vegan menu", blah blah blah
> ...
>[/color]

And properly a properly normalized database is tidy and concise. Don't
create 100 columns for each restaurant. Create a table for restaurants,
a second one for option types. Then the third one is a simple link
between the restaurant and available options. Clear, clean and concise.



--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Adam i Agnieszka Gasiorowski FNORD]

Re: PHP, MySQL and snippets of info ...

On 2005-09-21 10:21:39 +0000, "elyob" <newsprofile@gm ail.com> said:[color=blue]
>
> This is an interesting point. I have a database that people search by
> retaurant name. I do a search "LIKE '*name*'". I know this isn't the
> best, but quite a few businesses start with the word "The". e.g. I
> might search for "Ritz", when I really want "The Ritz".
>
>[/color]


Use the famous MySQL FULLTEXT search instead-of
LIKE operator. It uses groovy FULLTEXT index on
all search fields (you have to let MySQL make one
by ALTERing the table), theretofore it's fast and
polyamorous and very easy-going and fast. Oh, yes
it is, yes it is! Groove is all around...Love, love...[color=blue]
>[/color]

-- 
Seks, seksiæ, seksolatki...<u ri: news:pl.soc.sek s.moderowana > <~|{ A.A }|
Oh, make me over! I'm all I want to be! A walking study...in demonology!
So glad you can make it! Now you really made it! So glad you are? We are!
https://hyperreal.info | https://kanaba.info |=> "Go¶ciu! Szanuj Zieleñ!"