seperator instead of a comma for listed items in DB?

news@celticbear.com wrote:
> I'm making a recipe database, and need to have DB fields in mySQL that
> will have lists of values that would get sent to an array to be worked
> on.
>
> I anticipate that at times a comma will need to be used in the value
> itself. So, what's a commonly used symbol or something that people tend
> to use as a separator of a list that would then be used in the split()
> function to send the elements into an array?
>
> Nearly every punctuation has a chance of being in the actual data. Any
> suggestions?
>
> Thanks!
> Liam
>

Liam,

This violates first normal form (more than one value in a row/column).

Rather, create a second table containing the recipe id and a single value, then
add each of the values to this second table.

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

>I'm making a recipe database, and need to have DB fields in mySQL that
>will have lists of values that would get sent to an array to be worked
>on.

If you're putting a list of values in a single field, that represents
a one-to-many relationship, and calls for use of another table.
For example, a table called Ingredients which contains the
ingredient, quantity, and ID of the recipe to which it belongs.

>I anticipate that at times a comma will need to be used in the value
>itself. So, what's a commonly used symbol or something that people tend
>to use as a separator of a list that would then be used in the split()
>function to send the elements into an array?
>
>Nearly every punctuation has a chance of being in the actual data. Any
>suggestions?

Use another table.

Gordon L. Burditt

On Mon, 17 Apr 2006 08:12:58 -0700, news@celticbear.com wrote:

> Nearly every punctuation has a chance of being in the actual data. Any
> suggestions?

You can use a multi-character separator.

@@@

or
@!@

or

THIS_IS_THE_SEPARATOR

etc.


Who says a split() field separator has to be one character?
--
JDS | jeffrey@example.invalid
| http://www.newtnotes.com
DJMBS | http://newtnotes.com/doctor-jeff-master-brainsurgeon/

On Mon, 17 Apr 2006 12:09:24 -0700, news@celticbear.com wrote:

> Then the tbl_recipes would have colums like:
> ID, NAME, INGREDIENTS, MEASUREMENTS, NOTES
> with data like:
> 1, Chinese Chicken, 1|2|3|4, 1 clove|1 lbs. breast| 1 tsp.| 1 T., more
> stuff here....

You *could* do that but typically a reational database will use *another*
table to "link" the one-to-many relationship together.

table 1: ingredients
table 2: recipe
table 3: recipe-to-ingredients-linker

Table 1 and 2 would be as you described but table 3 would be (roughly) as
follows:

create table recipe_ingredients (
id int()
recipe_id int()
ingredient_id int()
... additional_columns ...
)

You then would join all three tables using recipe_id to join recipes to
the linker and then using ingredient_id to join to the ingredients.

This way, you can have an unlimited number of ingredients.

It is clunky from a *human* point of view but fast, flexible, scalable,
etc. from a machine point of view.

(The additional_columns could be things like "quantity" or other modifiers)

later...

--
JDS | jeffrey@example.invalid
| http://www.newtnotes.com
DJMBS | http://newtnotes.com/doctor-jeff-master-brainsurgeon/

Yeah I've used triple-colons in the past :::

for me it doesn't hurt my eyes to see that. :-)
--------
I typically lurk here as I keep trying to get my hands dirty w/ both
PHP & MySQL - but one issue I have - a road block really - is the setup
of a database and how it should be broken up, etc... There is some
nice insight here.

Thanks,
Scott

JDS wrote:
> On Mon, 17 Apr 2006 08:12:58 -0700, news@celticbear.com wrote:
>
> > Nearly every punctuation has a chance of being in the actual data. Any
> > suggestions?
>
> You can use a multi-character separator.
>
> @@@
>
> or
> @!@
>
> or
>
> THIS_IS_THE_SEPARATOR
>

IMO (and in my experience), relying on the assumption that certain
combinations characters won't appear in the actual data is just an
accident waiting to happen (I've had disastrous experiences based on
that assumption). Admittedly, @@@ or whatever is extremely unlikely to
appear in an ingredients list, but nevertheless...

If one must violate 1NF like this, I would recommend using a single
delimiter, such as a comma. If a literal comma ever occurs in the
actual data, it should be delimited by some means, e.g.:

Eye of toad
Leg, arm of newt
Brain of goat

becomes:

Eye of toad, Leg\, arm of newt, brain of goat

Separating the data on a read will become slightly more complicated
than a simple explode(), but it will be guaranteed to work in all
situations (I think).

But having said all this, by far the best option is not violating 1NF
in the first place, by storing this information atomically in a
separate table.


--
Oli