Ways to set and use tables relationship with online DB

Creating desktop or client-server database solutions?

Moderators: heatherlaine, Klaus, FourthWorld, robinmiller, kevinmiller

Post Reply
teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Ways to set and use tables relationship with online DB

Post by teriibi » Tue Feb 20, 2018 9:25 pm

Hi all :)

What are the most recent or efficient ways to set relationship between tables on a server for an SQL type online DB.
...I read a bit about SQL Yoga - never heard of before..(is Yoga outdated ?somehow :? now) :shock: if using PHP + SQL, which method would be most apropriate to create or link and also search tables within a single DB ¿

So far Im not decided if I should create tables and relatioship from control panel...or use PHP/SQL to do that on the fly. What are the pros and cons ?

Are there new fresh method or features to take most benefit from relationship tables with LC ?

thanks for any infos. :|

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 271
Joined: Tue Apr 10, 2012 9:18 am

Re: Ways to set and use tables relationship with online DB

Post by ghettocottage » Tue Feb 20, 2018 10:22 pm

Personally I like to use MySQL Workbench. It has a nice graph tool that lets you drag our your relationships, manage foreign keys, etc.

Once you are done you can connect to your server via SSH or direct DB connection and apply your model to your database.

Image

teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Re: Ways to set and use tables relationship with online DB

Post by teriibi » Wed Feb 21, 2018 1:59 am

Very interesting...tks
As for the rest, scripts that relates one table to the other its all made manualy or is that a feature of the bench too ?
:)

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 271
Joined: Tue Apr 10, 2012 9:18 am

Re: Ways to set and use tables relationship with online DB

Post by ghettocottage » Wed Feb 21, 2018 4:16 am

As for the rest, scripts that relates one table to the other its all made manualy or is that a feature of the bench too ?
MySQL Workbench is simply a GUI tool for working with MySQL databases and the features that are built in to MySQL.

Although you can just create your own relationships through your external PHP or Livecode scripts, there are some built-in features in MySQL that are nice to use (which Workbench or other tools can help you manage)
For example: Foreign Keys, which will help keep data consistent (amongst other things).

Another thing to look into is Views, which can combine fields from various tables to create a sort of "virtual" table...good for simplifying queries (amongst other things)


so, in short: you can just manually access tables create relationships from your scripts, but using some of the built-in features of MySQL and having a mapped out database structure can help a lot in keeping your data organized, consistent, and easy to access.

Post Reply

Return to “Databases”