Hallo, ik ben bezig met een simpele contactmanager webapplicatie.
Bestaat uit 4 tabellen (naam, adres,stad,zip) die onderling naar
elkaar verwijzen. Je kunt contacten invoeren, lezen, updaten en
verwijderen. Een contact lezen is makkelijk door de tabellen te
joinen. Maar als ik een nieuwe contact wil toevoegen hoe moet ik dat
doen? In elke tabel apart door na de eerste insert de last_insert_id()
op te vragen, die te inserten bij de volgende tabel als foreign key
maar eerst kjiken of de waarde die ik wil toevoegen niet al aanwezig
is?

Lijkt me erg complex worden... Ik heb begrepen dat zoiets 'Cascading
Table Insert' heet. Kan iemand me verwijzen naar een duidelijke
uitleg / tutorial hierover? Of zijn er tools voor die dit makkelijk
maken? Ik gebruik Dreamweaver 8, phpMyAdmin en Eclipse voor Coldfsion
en MySQL 5.0. Type tabel in de database is InnoDB.

Bedankt!

> Hallo, ik ben bezig met een simpele contactmanager webapplicatie.

Do you mind a reply in english? This is an international newsgroup.

> Bestaat uit 4 tabellen (naam, adres,stad,zip) die onderling naar
> elkaar verwijzen.

I would put adres, stad and zip in one table. An address consists of a
street, a city and a zip code. They are not really separate entities. If
you allow more names per address, you should keep the naam table separate.

> Je kunt contacten invoeren, lezen, updaten en
> verwijderen. Een contact lezen is makkelijk door de tabellen te
> joinen. Maar als ik een nieuwe contact wil toevoegen hoe moet ik dat
> doen? In elke tabel apart door na de eerste insert de last_insert_id()
> op te vragen, ...

There is always an independent entity that is there first, so you can
"attach" the other tables to it. If an address is your main entity, you
can enter an adress without inhabitants, and add inhabitants to that
address.

>die te inserten bij de volgende tabel als foreign key
> maar eerst kjiken of de waarde die ik wil toevoegen niet al aanwezig
> is?

How do you check if it is not already present? Duplicates can occur. Is
you have streets in a separate table, there may be streets that exist in
more than one city. "Stationsstraat", for example.

>
> Lijkt me erg complex worden... Ik heb begrepen dat zoiets 'Cascading
> Table Insert' heet.

There is no such thing as a cascading insert (although you could do it
with triggers). For foreign keys, only cascaded deletes and cascaded
updates exist. Although I cannot think of any sane situation where you
would need a cascaded update.


Best regards.

Sorry for not speaking international... I'll repeat the question in
English.

I have a contactmanager - webapp (Coldfusion-MySQL 5.0). A contacts
contains a name, address, city, zip. I have separate tables for these
entities, pointing to each other using foreign keys. Retrieving a
contact is easy joining those tables. Adding a contact is less
straightforward since you need the id from table A _before_ inserting
a new value in table B that points to a value in table A. Worse, you
even don't know if the value in table A exists so you'd have to check
that first.

This makes for many statements to insert a value.

I could easily put Name, Address, City and Zip in 1 table without too
much of a performance penalty. Then I'd be using MySQL as an ordinary
flat file database. I am just wondering - what if I want to adhere to
the relational model?
Example:

I have 1 table 'cities' with unique cities and 1 table 'adresses' with
(not unique) streets. Every street in the 'addresses' table is
referring to the proper city in table 'cities'.

Same with table 'zipcodes': many zipcodes point to the same city so I
want a many - to - one relation from table 'zipcodes' to table
'cities'.

How do I insert a new combination 'address - city' in those 2 related
tables? I guess:

1 First do a select on table 'cities' to check if the city exists;
2 if the does not exist I insert it in table 'cities',
3 I select the last_insert_id() from table 'cities';
4 I insert that value as a foreign key with the zipcode in the table
'zipcodes'.

Or, if the city does exist, I skip step 2.

Maximum 4 queries to insert 1 item in 2 tables. What if I have more
tables? This is going to be complicated... There must be a simple
solution. I wonder how is this done in relational db systems???

Thanks for any tips, pointers to articles etc!

zephyr wrote:
> Sorry for not speaking international... I'll repeat the question in
> English.
>
> I have a contactmanager - webapp (Coldfusion-MySQL 5.0). A contacts
> contains a name, address, city, zip. I have separate tables for these
> entities, pointing to each other using foreign keys. Retrieving a
> contact is easy joining those tables. Adding a contact is less
> straightforward since you need the id from table A _before_ inserting
> a new value in table B that points to a value in table A. Worse, you
> even don't know if the value in table A exists so you'd have to check
> that first.
>
> This makes for many statements to insert a value.
>
> I could easily put Name, Address, City and Zip in 1 table without too
> much of a performance penalty. Then I'd be using MySQL as an ordinary
> flat file database. I am just wondering - what if I want to adhere to
> the relational model?
> Example:
>
> I have 1 table 'cities' with unique cities and 1 table 'adresses' with
> (not unique) streets. Every street in the 'addresses' table is
> referring to the proper city in table 'cities'.
>
> Same with table 'zipcodes': many zipcodes point to the same city so I
> want a many - to - one relation from table 'zipcodes' to table
> 'cities'.
>
> How do I insert a new combination 'address - city' in those 2 related
> tables? I guess:
>
> 1 First do a select on table 'cities' to check if the city exists;
> 2 if the does not exist I insert it in table 'cities',
> 3 I select the last_insert_id() from table 'cities';
> 4 I insert that value as a foreign key with the zipcode in the table
> 'zipcodes'.
>
> Or, if the city does exist, I skip step 2.
>
> Maximum 4 queries to insert 1 item in 2 tables. What if I have more
> tables? This is going to be complicated... There must be a simple
> solution. I wonder how is this done in relational db systems???
>
> Thanks for any tips, pointers to articles etc!
>

There is nothing wrong with keeping everything in the same table if that
is the appropriate way to do it. You shouldn't over-normalize just
because you think you should use multiple tables.

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