Professional Web Applications Themes

Cascaded insert in MySQL? - MySQL

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

  1. #1

    Default Cascaded insert in MySQL?

    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!

    zephyr Guest

  2. #2

    Default Re: Cascaded insert in MySQL?

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

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

    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.
     

    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.
     

    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.
     

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

  3. #3

    Default Re: Cascaded insert in MySQL?

    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 Guest

  4. #4

    Default Re: Cascaded insert in MySQL?

    zephyr wrote: 

    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.
    net
    ==================
    Jerry Guest

Similar Threads

  1. id after insert in MySQL
    By Ike in forum PHP Development
    Replies: 1
    Last Post: June 24th, 02:07 AM
  2. insert CSV in mySql problem
    By Richard Sauve in forum PHP Development
    Replies: 1
    Last Post: April 30th, 09:05 PM
  3. insert combobox into MySQL db
    By rigpig in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 27th, 06:25 PM
  4. Replies: 7
    Last Post: July 29th, 03:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139