473,320 Members | 2,145 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

PHP, MySQL and snippets of info ...

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
Sep 20 '05 #1
9 1846
>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
Sep 20 '05 #2
elyob (ne*********@gmail.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_accepts_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_accepts_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.
Sep 20 '05 #3

"Gordon Burditt" <go***********@burditt.org> wrote in message
news:11*************@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

Sep 20 '05 #4
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.


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
Sep 21 '05 #5
>> >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
Sep 21 '05 #6

"Gordon Burditt" <go***********@burditt.org> wrote in message
news:11*************@corp.supernews.com...
Incidentally, what ARE Bc and Ca? Bank debit card and cash?
Bank of British Columbia and State of California Food Stamp cards?

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.
Um, *WHAT TYPE OF* overhead? Are you concerned about disk space here?
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_type".
(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). 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.
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
....
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.


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

Sep 21 '05 #7

"ManChild" <st******@gmail.com> wrote in message
news:xy*******************@tornado.rdc-kc.rr.com...
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.

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.


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.

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!


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.

Sep 21 '05 #8
elyob wrote:


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.

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.
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.
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?
So, rather than expand my table with 6 extra columns, I just add one column
with called "payment_type".

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


Maximum processing time encoding/decoding the column, maximum code
changes when adding a new payment type...


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


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.
js*******@attglobal.net
==================
Sep 21 '05 #9
On 2005-09-21 10:21:39 +0000, "elyob" <ne*********@gmail.com> said:

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

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


--*
Seks, seksić, seksolatki...<uri: news:pl.soc.seks.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ń!"
Oct 1 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Tom Martin | last post by:
I'm a Java Web developer NEWBIE that has inherited a website that fails every 2 hours due to poor connection pooling between Tomcat 4.0.6 and mySQL. In efforts to resolve this problem, I've...
0
by: Chris Moy | last post by:
SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `<' and `>'). SEND-PR: From: moyc To:...
0
by: I.P. | last post by:
Hi, it's my story. I have two 4.0.14 mysql server on one machine with win XP Professional polish version. First acts as master: on port 3300 Second acts as slave: on port 3301 below my...
0
by: I.P. | last post by:
No one has replied to my post. ----- Original Message ----- From: "I.P." <jancio_wodnik@wp.pl> To: <mysql@lists.mysql.com> Sent: Monday, August 18, 2003 1:01 PM Subject: mysql 4.0.14 +...
0
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest...
1
by: Maxim. V. | last post by:
Hello to everone, As mentioned in this *guide(1), i've compiled mysql on my fedora Core 2 system, with the following spec's : *(1) http://hulan.info/blog/index.php?itemid=576 ...
11
by: Howard | last post by:
Hi, I have been racking my brain trying to figure out what I am missing trying to follow this book to get a php script to work. I would really appreciate if someone could point out my errors....
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
6
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.