multiple related tables

gforster

I'm not really sure how to ask this (which is likely why I am stuck).

I have two separate MySQL tables called "SERVER" and "VOLUME." In the SERVER table, there is a foreign key for the VOLUME table. Vice versa in the VOLUME table. It is simple enough to do the left joins and set those up on their own, no issue there.

What I would like to do is when I edit the SERVER table with a particular VOLUME, the VOLUME table also correctly references the SERVER. Does that make sense?

e.g. If I set this on the SERVER table
SERVER
id=1
name=myserver
volume_id=2 (the foreign key)

I would like when I set the volume_id above, it does this on the VOLUMES table:
VOLUMES
id=2
name=myvolume
server_id=1 (the foreign key)

rf1234

I think this does not work. But what is the logical relationship between the two entities?

Is it 1:N (One Server can have n volumes). Or is it m:n (servers can have multiple volumes but volumes can be assigned to multiple servers as well).
If it is the first case you do not have a foreign key of volume in the server table, but you have a foreign key of server in the volume table.
If it is the second case you need a link table between server and volume. That means the link table holding both primary keys establishes the link and there are no foreign keys in either volume or server.

I a made a quick ER-model in MySQL workbench and attached it. It shows the first case server and volume, and the second case as otherserver and othervolume with the link table in between.

rf1234

Forgot to add that. Here is a link that explains ER-modelling: https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model

Many people use PHPMyAdmin with MySQL. I wouldn't do that. You don't really get to modelling properly and it really doesn't visualize your model. MySQL Workbench is so much better and it generates all the DDL (data definition language) based on your model. In practice this means that you draw the relationships between your tables and the foreign keys are generated automatically for you as needed. Same applies to the link tables that may or may not be required.

gforster

Thank you for your reply. I am following your logic. It would be the first case. Each volume is on one (and only one) server.

When it comes to displaying datatables, I have one that shows all the details of the volumes, and one that has all the details of the servers. For the one displaying Volumes, I do the left join and include the server.name. All is good.

For the datatable displaying the Server, I want to also have a column that says "Hey, this is the volume I'm associated with."

rf1234

But if you have a 1:n relationship the column on the screen (not in the database) should rather say "Hey, these are the volumes I'm associated with." And then you would list those volumes. (I am not a hardware person ... I don't even know what a volume is, sorry).
Or do you have a 1:1 relationship? This would only make sense if it is optional for a server to have a volume but if it has a volume it is exactly one. If every server has exactly one volume they should jointly reside in one table, unless there are abandoned volumes that don't have a server ...

So let's assume it is 1:n. In this case you could do an Mjoin on the foreign key. Mjoin will return an array of n elements (the volumes that are associated with the server). This array you can display in your table column on the screen.

->join(
    Mjoin::inst( 'volume' )
        ->link( 'server.id', 'volume.server_id' )
        ->fields(
            Field::inst( 'volume.name AS volumeName' )->set( false )
        )
    )

Now in Javascript you could do this for example:

{   data: null,
    render: function ( data, type, row ) {
        var ix=0;
        var returnString = '';
        while (row.volume[ix]) {
            if (ix !== 0) {
                returnString = returnString.concat('<br>');
            }
            returnString = returnString.concat
                (row.volume[ix].volumeName);
            ix++;       
        }
        return returnString;
    }
},

This should display all the volumes for your server with a line break between each volume name.

rf1234

Regarding left join I forgot to mention that if you do a left join and you have one server that has say three volumes then the left join would return three rows which you probably do not want. So you definitely need the mjoin if you want to display all the volumes in one row.