Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Jul 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a MySQL schema that reflects a JSON object

    I have the below JSON object and am curious how to create a MySQL schema to reflect it. I am new to creating CRUD apps and am in need of some direction.

    Summary:

    Each user in my application can create as many 'patch' objects as they want (each needs to be stored in MySQL) hence the JSON below is a snap shot of just one of them. Within the patch object there are synth objects of which the user might have hundreds of within each patch object.



    Code:
    var patch = {                               // This entire object is a 'patch' item
    "patch_name": "oscillating do-dad",
        "sound_type": {
            "synth": [     
            {synth_name: "synth1",xpos:180,ypos:45}, // There could be hundreds of these things
            {synth_name: "synth2",xpos:200,ypos:45},
     
             
            ],
    
        }, 
     
    }

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Code:
    CREATE TABLE patches (
        patchid INT AUTO_INCREMENT PRIMARY KEY,
        patch_name VARCHAR(100),
        sound_type VARCHAR(100)
    ) ENGINE INNODB;
    
    CREATE TABLE synths (
        patchid INT,
        synth_name VARCHAR(100),
        xpos INT,
        ypos INT,
        CONSTRAINT FOREIGN KEY (patchid) REFERENCES patches(patchid)
    ) ENGINE INNODB;
    
    INSERT INTO patches (patch_name, sound_type)
    VALUES('oscillating do-dad', 'synth' );
    
    // in PHP code, use mysql_insert_id or equivalent to get the patchid of the just inserted record
    
    INSERT INTO synths (patchid, synthname, xpos, ypos )
    VALUES( $patchid, 'synth1', 180, 45 ), ($patchid, 'synth2', 200, 45 );
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

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