MySQL ODBC Connector - Non-destructive Expanding & Retracting sets

Author

Non-destructive Expanding & Retracting sets

Andy Pieters

2005-03-31, 8:03 pm

--nextPart2478777.yaXLYv3d1i
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all

=46acing the following problem

In a record there is a field category.
Categories can be added/changed/removed

If I would use a fieldtype of set if would have been destructive in case a=
=20
category got removed.

So I implemented by using a text type field and some extra validation=20
routines.

Can this be done more directly and more INSIDE mysql?

Example data

id type =09
Page 1 category1,categor
y2
Page 2 category3
Page 3 category2,categor
y4

I currenlty do my selects with
SELECT `id` FROM `thetable` WHERE `type` LIKE '%category3%';

But I suspect speed and other penalties may arrise when the database is rea=
lly =20
in production.
=2D-=20
Registered Linux User Number 379093
=2D-
=46eel free to check out these few
php utilities that I released under the GPL2 and=20
that are meant for use with a php cli binary:
http://www.vlaamse-kern.com/sas/
=2D-

--nextPart2478777.yaXLYv3d1i
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBCTBVfP6e1Rq5I
UAkRAgCzAKC+nrxhe6uY
XqUjxoMJhGKvQSSmBgCf
afMB
IyGGXoVwA5NVhMDRxp+P
b4w=
=uJkE
-----END PGP SIGNATURE-----

--nextPart2478777.yaXLYv3d1i--

Jason Martin

2005-03-31, 8:03 pm

--xHFwDpU9dbj6ez1V
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Thu, Mar 31, 2005 at 05:21:00PM +0200, Andy Pieters wrote:
> In a record there is a field category.
> Categories can be added/changed/removed
> Example data
>=20
> id type =09
> Page 1 category1,categor
y2
> Page 2 category3
> Page 3 category2,categor
y4
=46rom a Normal Form perspective, this is supposed to be
implented as=20
Page 1 category 1
Page 1 category 2
Page 2 category 3
Page 3 category 2
Page 3 category 4

at which point adding / removing categories is just a simple
insert or delete.
> I currenlty do my selects with
> SELECT `id` FROM `thetable` WHERE `type` LIKE '%category3%';
That query will not be able to use indexes and will be too slow
to use in any sizable database. =20

-Jason Martin
--=20
Nobody home but the lights, and they're out too.
This message is PGP/MIME signed.

--xHFwDpU9dbj6ez1V
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: --no-verbose

iD8DBQFCTBbwl2ODWuqV
SBMRArs2AJ9BwG5+uhCf
4VSahs0hIA10zS9xCQCf
Ycsq
wqHRDlupgRdmV6Zh2tgz
jno=
=xMfH
-----END PGP SIGNATURE-----

--xHFwDpU9dbj6ez1V--

Andy Pieters

2005-03-31, 8:03 pm

--nextPart2051149.hx3DHtljqv
Content-Type: text/plain;
charset="iso-8859-15"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On Thursday 31 March 2005 17:27, Jason Martin wrote:
> From a Normal Form perspective, this is supposed to be
> implented as
> Page 1 category 1
> Page 1 category 2
> Page 2 category 3
> Page 3 category 2
> Page 3 category 4
>
> at which point adding / removing categories is just a simple
> insert or delete.
>

Blimey!

The perl phrase "There is more then one way to do it" comes to mind

Eyes are wide open now!

Thanks

Andy
=2D-=20
Registered Linux User Number 379093
=2D-
=46eel free to check out these few
php utilities that I released under the GPL2 and=20
that are meant for use with a php cli binary:
http://www.vlaamse-kern.com/sas/
=2D-

--nextPart2051149.hx3DHtljqv
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBCTBhWP6e1Rq5I
UAkRAsYZAJ4/ OVEGQM5qMktuBlXzJaxe
gReYVgCggHYK
g8Bl+OgERLQ2bZb5qlGm
N6o=
=k5ne
-----END PGP SIGNATURE-----

--nextPart2051149.hx3DHtljqv--

Sponsored Links