Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
creating relationships

Posted by 4est (4est), 18 June 2003
Hello,

        I am making the plunge from Access into MySQL to drive my ASP site.  I made my db with the control center and am now using access as an interface.  I am disturbed by the fact that I am unable to view/assign relationships except in access.  I can, of course, designate the relationships in access, but this doesn't seem to change the structure of the db when looked at in control center.  so i guess i have a multiple choice ?.

what should i do:

1) designate relationships w/o using access.  If so,  where could i learn how?

2) use the .mdb file from access containing my imported MySQL tables as the datasource for my site instead.  would that method enforce the relationships? would that cancel out all the performance benefits I get from using MySQL?

3) enforce the relationships w/code and not worry about the lack of db-defined relationships.

4) do something totally different.  what?

Thank you very much for any advice or information.  I hope I am not asking too idiotic of a question, if so i apologize.

4est.

Posted by admin (Graham Ellis), 19 June 2003
Good questions / thoughts. MySQL is a very different beast to Access - in switching across from one to the other, you'll loose something and gain something.

MySQL in common with most other databases (but not in common with Access) runs as a daemon process (a.k.a. a service) that's provided as a relational database access tool for whatever application wishes to access it.  Strengths of this approach include the ability for lots of clients to access the same data under a single set of controls, thus allowing the solution of multiple different applications wanting to change the data at the same time, the requirement to have only one set of drive code (not many) running at the same time, easier network access, a semi-standard in the form of SQL making application code much more portable, etc.   One of the weaknesses of the approach is that some of the nicer features that aren't included in the Structured Query language / are more easily implemented in a single program rather than the wider distributable application are no longer available to you; MySQL is characterised as a compentent, fast, stable database that's easy to configure and use.

Enough of an introduction;  the answer to your questions is really "you do it differently in MySQL".

You'll typically be accessing data held in MySQL through your ASP client, and not through the utilities supplied as part of the MySQL distribution such as "mysql" (the main program is "mysqld").  In your ASP client, if you want to INSERT rows into multiple tables to generate a complete information set do so - use multiple insert commands.   Set up the relationship between the data in the multiple tables by using a key field - either selecting a unique value in one table that you'll use to relate to the other, or if you don't have a unique key - invent one.  When you come to pull out data that relates, use a SELECT command on multiple tables, and specify how the tables are to be related within the select command.   For example
     SELECT * FROM people, homes WHERE home.id = people.hid

Big subject - there's lots of ways of doing joins (this really is a join, but we don't have to put the word in), including ways to join and find orphan data in one table or the other ... and you can joing more than 2 tables at a time ....

You ask "where could I learn ...".   Well - I've just finished running a coures on MySQL, and there's another one in August (18th and 19th). I could of course recommend that - see
http://www.wellho.net/course/mqfull.html.  There's also a lot of books available - the 35 we have on the shelves at our training centre are listed at http://www.wellho.net/resources/mysqlbooks.html and we have highlighted our favourites and ranked the books from "absolute beginner" through to "supergeek".   Links of the individual books take you to detailed information on each, and links to Amazon in the UK and/or USA.

Posted by waygood (waygood), 1 July 2003
There are a few tools out there to convert you access database to mysql if you want a starting point. See the Mysql website for "Using MySQL with other products".

I used one of their products for a day or two, but found working direct with mysql much easier. The good points of Mysql made me ask why would anyone consider using Access. The simplest additional feature you will come across is LIMIT.

SELECT * FROM table  give all rows BUT
SELECT * FROM table LIMIT 10 will only give the first 10.
SELECT * FROM table LIMIT 10,3 will ignore the first 10 and give the next 3 rows.

Seems pretty insignificant until you start working with large databases and you dont want people to wait 10 minutes for the query to finish. (ever used Microsofts website??). With Access you must get all the results and use the ones you need, which is very memory inefficient.

I am currently working on a database with 2 million large records. I am manipulating the data locally to get it ready for the web, and the categorising queries take 30 minutes to complete, but the retrieval queries take only a second.

As far as your relationships go, just put them into your queries. You can either JOIN them or put the criteria in the WHERE clause, depending on what you  need.
Just like access you can do INNER, LEFT, OUTER, RIGHT joins, see http://www.mysql.com/doc/en/JOIN.html.

simple example:

SELECT table1.*
FROM table1, table2
WHERE table1.table_two_id=table2.id

OR

SELECT table1.* FROM table1
LEFT JOIN table2 ON table1.table_two_id=table2.id
WHERE table2.table_two_id IS NOT NULL

Posted by admin (Graham Ellis), 7 November 2008
Follow up (via email) that I've received on this thread ... posted here to help complete the picture.

Quote:
Dear Graham,

I came across your page http://www.wellho.net/forum/The-MySQL-Relational-Database/creating-relationships.html whilst doing some research into database engines.

I couldn't help noticing that the following sentence is incorrect:
"With Access you must get all the results and use the ones you need, which is very memory inefficient.
Similar to the LIMIT keyword, Access supports the syntax SELECT TOP x ... which will only return the specified number of results.
You might want to get someone to look into it and revise this as it could be misleading for people."

Otherwise, I am grateful for the information on the site and it has been useful.

Thanks and Kind Regards


I'm delighted to have received further follow up - even five years later - as it helps show that these pages and their archives remain useful.  And indeed I know from my web logs that hundred and hundreds of people read these items for every one who sends feedback - which is genarally positive.

[Above quote reproduced with permission, no credit sought]





This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2014: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho