Another MySQL Question

[Victor Subervi]

On Thu, Jun 3, 2010 at 4:09 PM, John Nagle <nagle at animats.com> wrote:

>   The real problem with this is not the Python.  It's the approach
> to SQL.  What's going on here is that you have some collection of
> named options that come in from some external source, and you're
> trying to handle that by dynamically constructing the table schema.
>
>   It would be easier to store the "option" values in a separate
> table, with something like
>
>        CREATE TABLE temp01 (
>                entryid BIGINT NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,
>                Store VARCHAR(40) NOT NULL,
>                PatientID VARCHAR(40) NOT NULL,
>                ProdID VARCHAR(40) NOT NULL,
>                Pkg VARCHAR(10) NOT NULL)
>                ENGINE=INNODB;
>
>        CREATE TABLE ouroptions (
>                FOREIGN KEY (parententry) REFERENCES temp01(entryid)
>                        ON DELETE CASCADE,
>                optionname VARCHAR(40) NOT NULL,
>                optionvalue VARCHAR(40) NOT NULL)
>                ENGINE=INNODB;
>
>   This creates two tables which are locked together.  For any entry
> in "temp01", you can create any "option" entries you need in "ouroptions".
> If you delete the entry in "temp01", the related "options" entries will go
> away.
> (That's what ON DELETE CASCADE does.) When inserting, insert the record in
> temp01 first, then the recrods in "ouroptions", in a single transaction.
>
>   Incidentally, if you don't specify some indices, lookups will
> take a very long time.
>

Your solution is very elegant; however, the application here is
inappropriate. I'd been contemplating creating a temp table like you
suggest, but in this application, there is only need for one table. However,
you've given me an idea that will no doubt come in helpful in the future ;)
Thanks,
beno
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100605/f6954b25/attachment-0001.html>