Relationships in MySQL [help and/or listen to me rant]

[AHunter3]

So I’m futzing around seeing to what extent I can get a handle on MySQL from a manual (Vaswani’s How to do Everything with PHP & MySQL) as recommended by Lobsang in this thread, and drawing upon my own FileMaker experience.

I selected a vintage-1996 FileMaker db, my first serious FmPro db in fact, figuring it was “real world” (at one time) and yet pretty simple compared to stuff I’ve done since then…

I am now officially confused, folks. And I do not like it when I don’t understand things. I do not like feeling stupid.

This manual sort of skips over how the heck you SET UP A FREAKING RELATIONSHIP, which is odd because it provides specific walk-thrus and example command-line syntax for the creation of databases, creation of table, and defining of fields. But of relationships it sort of glosses past, explaining only “this is how you define a field as your primary key” and then mentioning that this will be used in conjunction with foreign keys, e.g.,

@Vikram Vaswani

With a relational database system like MySQL it’s also possible to link information in one table to information in another * . When you begin to do this, the true power of an RDBMS becomes evident.

(p 152)

• Gee, Ya THINK!??:smack:

@Vikram Vaswani

You can specify a foreign key for a table with the FOREIGN KEY modifier. The FOREIGN KEY modifier links a field in one table to a field (usually a primary key) in another table, setting up a base for relationships.

(p. 170)

Yeah yeah yadda yadda… so where the hell is the syntax for create relationship, dammit?

The example given makes it sound like simply by creating some fields in different tables that happen to have the same field names, some magic occurs presto connecto wowzy woo woo, suddenly there’s a relationship here!?

Why isn’t there any walk-thru?
OK, if you were Mr. Vikram Vaswani, I would want to be asking you to explain the following:
a) Let’s say I have TableOne which has a defined primary key field “employeeserial” an integer field not null and auto incrementing; and I have TableTwo which consists of records each of which will be associated with one and only one record from TableOne. I know what I’d do in FileMaker, I’d define a field in TableTwo, let’s say it’s also called “employeeserial” and it’s just a plain ol’ number field (we FileMaker folks do not have to wave any flags and say THAT THERE is a FOREIGN KEY; I gather that in MySQL I would do so, though?) and then define a relationship between the two like so:

TableOne::employeeserial = TableTwo::employeeserial

OK walk me through how I do that part in MySQL please? Just saying “Yo, you there, you’re a freaking FOREIGN KEY” doesn’t tell it what field in what table it is supposed to hook up with, right? I mean how the heck could it? Especially since… well let’s just continue…
b) For kicks, let’s assume that there exists a field in TableTwo called “status”, varchar (16) which you’d do in FileMaker simply by creating a text field “Status”; and this field may contain several different text values one of which is “Inactive”; and that I wish to establish a second relationship between TableOne and TableTwo, such that employeeserial in TableOne equals employeeserial in TableTwo AND the field status in TableTwo does NOT equal “Inactive”. I can do that in several easy ways in FileMaker: I can create a calcfield in TableTwo of result type “number” defined as Case (Status≠"Inactive", employeeserial) and then define my new relationship like so:

TableOne::employeeserial = TableTwo::newcalcfield

OR, instead I can define a calcfield in TableOne result type “text” defined simply as the constant “Inactive” and then define my relationship this way instead:

TableOne::employeeserial = TableTwo::employeeserial AND
TableOne::inactiveconstant ≠ TableTwo::status

(in modern FileMaker that would result in a second table occurrence of TableTwo on the relationship graph)
OK walk me through how I create a second relationship between two already-related tables in MySQL? I guess if I knew how to explicitly create the FIRST relationship I’d know this as well?
c) Also of interest, the selfjoin relationship, if you don’t mind. If I wish that all records in TableTwo that share the same status field value be related to each other, how do I tell MySQL to relate the table to itself in that fashion? In FileMaker, I’d make a rel between TableTwo::status and itself, which would once again prompt a new table occurrence which I’d give some useful name like TableTwoSameStatus.

[ultrafilter]

In an SQL database, relationships are basically what we refer to as syntactic sugar, meaning that they’re nice to have, but you can get by without them just fine. What you’re looking for is the inner join operation. If you don’t know SQL, joins can be a little tough to wrap your head around at first, so take some time to read through that article.

[RaftPeople]

c) Also of interest, the selfjoin relationship, if you don’t mind. If I wish that all records in TableTwo that share the same status field value be related to each other, how do I tell MySQL to relate the table to itself in that fashion? In FileMaker, I’d make a rel between TableTwo::status and itself, which would once again prompt a new table occurrence which I’d give some useful name like TableTwoSameStatus.

select
t1.*
,t2.*
from TableTwo t1
inner join TableTwo t2
on t1.status=t2.status
That’s going to give you a bunch of stuff * 2 (one for each pair of rows such that any row from TableTwo occupies t1 and another where that same row occupies t2), is status really the only field you would want joined on? What is the goal? Knowing that might help fine-tune the above sql.

[ZipperJJ]

To re-iterate what the others said - at this stage in the game you do not need to specifically imply anywhere that anything is a “foreign key.” The idea of “foreign key” can just be in your head.

A good rule of thumb is to make the PK in a table an auto-increment int-type field called ID. Then, when you reference that field in another table, call the field [othertablename]ID.

So you can have a table called Customers with a field called ID, and a table called Invoices with a field called ID and a table called Salespeople with a field called ID. Then in your Invoices table, have CustomerID and SalespeopleID (even tho SalespersonID makes more sense, stick with the table name anyway). Now you have two “foreign keys” in your Invoices table but they only exist as “foreign keys” in a logical sense and there’s no actual constraints.

And, one more rule of thumb - the most confusing thing for beginners about inner joins is that when you do an inner join you only get records returned where each of the joined tables have data. So if you want to get all customers don’t inner join Customers with Invoices because if a customer doesn’t have an invoice, you’ll get no record.

The key to foreign keys is to not have to duplicate data across tables. All customer info is in the Customers table and all Invoice data (no customer phone, address, name, etc) is in the Invoice table etc. This saves you time and resources by not having to update data in a bunch of places when data changes.

[AHunter3]

So “relationships” in MySQL are not even nouns, they only exist when you get around to doing stuff “as verbs” like when you run QUERIES or INSERT statements & whatnot?

I had already sort of picked up on the idea that there is no such thing as a “calculation field” in the FileMaker sense of the word: a field whose contents is dynamic and is the result of a formula that references (or at least CAN reference) related data. Are there also no auto-enter (default) options in which you enter a formula rahter than a hardwired value (i.e., fieldX has a default value of 13 times sum ( Relatedtable::somefield) + SomeotherRelatedTable::someotherfield, that kind of thing?

This thing doesn’t even seem like a database to me, let alone a relational database. There’s no there there aside from rows and columns! It could all just be spreadsheets!

The PHP side of this endeavor had better be some kind of fantastic, as it looks like it’s going to be doing nearly all the heavy lifting.

[RaftPeople]

Re: calculated fields: You can create a view that has multiple tables, calculated columns, etc.

[RaftPeople]

There’s no there there aside from rows and columns! It could all just be spreadsheets!

What’s a “there there”?

[ZipperJJ]

Real databases are kind of hard to wrap your head around at the beginning, especially if you’re used to using a GUI-for-data like Excel or FileMaker, or even Access.

In those you are using a program that gives you the ability to do stuff like “make forms” or run calculations using tools built right into the application.

Databases like MS SQL or MySQL are just places to store data in tabular form. Then, you take whatever programming language you want and build whatever sort of application you need that does nothing but retrieve and manipulate data from your database.

vBulletin (this board’s software) is an example of such a program.

In fact, PHPMySQL and MS’s SQL Server Management Studio are also such programs. They are pre-built GUIs for databases. You can also, with the right drivers, access and manipulate Excel and Access data without ever touching the GUI, all through code.

So, learning to create a well-structured database is one thing. Learning what commands you can run on said database is another. Learning how to create the code that runs the commands is yet another thing.

Anyway, once you learn all this and become comfortable with what a database is and how it relates to real-world projects, you see everything in a completely different light.

It will come.

[Do_Not_Taunt]

@ultrafilter

In an SQL database, relationships are basically what we refer to as syntactic sugar, meaning that they’re nice to have, but you can get by without them just fine. What you’re looking for is the inner join operation. If you don’t know SQL, joins can be a little tough to wrap your head around at first, so take some time to read through that article.

I don’t think this is quite right - making your foreign keys explicit has a purpose beyond syntactic sugar: specifically, you can enforce referential integrity checks and automate cascading operations. For an example of the former, if you have a customers table with an id, and an invoices table with a customer_id, having an explicit foreign key prevents you from setting the value of customer_id of an invoice to, say, 1000, if there is no row in the customers table with id=1000. As an example of the latter, if you delete the row from customers with id=500, you can have the database automatically delete all the invoices with customer_id=500. This could be helpful, or completely disastrous, depending of the sematics of your situation.

[Ruminator]

@AHunter3

So “relationships” in MySQL are not even nouns, they only exist when you get around to doing stuff “as verbs” like when you run QUERIES or INSERT statements & whatnot?

Relationships can be part of “verbs” when doing SQL SUDI (SELECT, UPDATE, DELETE, INSERT).
But it can also be defined as “nouns” when using options such as CONSTRAINT FOREIGN KEY REFERENCES clauses like this:

ALTER TABLE (table_name)
ADD
**CONSTRAINT** (foreign key constraint name)
**FOREIGN KEY** (field name)
**REFERENCES** primary_table_name
(primary_table_primary_index_field);

Keep in mind that explicitly defining relationships CONSTRAINT FOREIGN KEY REFERENCES are not necessary. For example, multi-billion dollar SAP business software has 20,000+ tables defined and their programmers don’t bother with creating “noun” relationships via CONSTRAINT FOREIGN KEY. All of their millions of line of source code just joins the tables as needed via SELECT. They certainly describe/document the relationship of their 20,000 tables in pictures (entity-relationship diagrams) but they don’t rely on the database engine to enforce parent-child relationships. There is some wisdom in why they deliberately do this which I won’t go into here.

However, databases with “data warehouse” applications often explicitly define “noun” relationships so the db engine can create “star indexes” for faster filtered retrieval.

This thing doesn’t even seem like a database to me, let alone a relational database. There’s no there there aside from rows and columns! It could all just be spreadsheets!

I guess thinking about “what makes a database a real database” can get into philosophical disagreement but I suggest thinking about them in a different way.

For example, a desktop database like Microsoft Access or FileMaker will have built-in reports & GUI forms to interact with the database. Someone coming from that world will look at MySQL, MS SQL Server, Oracle and be bewildered why they don’t come with those features. Oracle costs $200,000 and it doesn’t have native report capability?! (I know the big database guys now all include add-on “packages” for reports such as Crystal Reports, but that’s not quite the same native integration as MS Access / FileMaker.)

The real differentiation of enterprise databases is the storage engine: the complex engine to handle hundreds/thousands simultaneous users and the intelligent allocation of data blocks to manage terabytes/petabytes of millions/billions of rows. This is what spreadsheets/FileMaker cannot do. The “relationship” syntax is really minor in the big scheme of things.

[AHunter3]

I know it was mostly a very well-written storage-and-retrieve engine, but I was expecting a little bit more structure than this!

Yes, I would benefit greatly if there were s website or a good book about SQL system written explicitly for people coming in from FileMaker. They do that for folks switching from Windows to Mac, which is not as big a leap.

[ZipperJJ]

@AHunter3

Yes, I would benefit greatly if there were s website or a good book about SQL system written explicitly for people coming in from FileMaker. They do that for folks switching from Windows to Mac, which is not as big a leap.

Honestly, since you’re such a pro at FileMaker, you might consider starting a blog that documents your journey through learning how to work with MySQL when coming from the FileMaker world. In fact, that might be a good first project for you for learning - build the blog. Blogs are good for having nice simple databases with some relationships (authors, posts, categories, etc) and lookups (by date, by category, by author, etc).

Of course, you’d also have to learn a language that would enable you to present the data on the Web (PHP) but you might find that your entire experience is very limited if you are just working with a database alone.

I find that a project with a goal is a great way to force yourself into learning. Reading books is nice but very dissatisfying in the end.

[ultrafilter]

@Do_Not_Taunt

I don’t think this is quite right - making your foreign keys explicit has a purpose beyond syntactic sugar: specifically, you can enforce referential integrity checks and automate cascading operations.

Definitely, but relationships in Filemaker or Access are something above and beyond foreign keys.

[Do_Not_Taunt]

@ultrafilter

Definitely, but relationships in Filemaker or Access are something above and beyond foreign keys.

Color me confused - first, wasn’t the OP a question about how to do something in MySQL, not Filemaker or Access? And secondly, as a non-filemaker/access user, what do you mean by this? If relationships in these products are “above and beyond” foreign keys, wouldn’t that mean they can be used for everything FKs are used for and more? Which would mean that my statement about how FKs are not merely syntactic sugar would apply equally or moreso to relationships?

[ultrafilter]

Relationships are above and beyond foreign keys exactly in the sense that they are syntactic sugar. In Access, you set up relationships by laying out graphics representing your tables in a window and drawing lines between primary keys and foreign keys. But it doesn’t do anything that an SQL CREATE TABLE statement with appropriate foreign key syntax doesn’t do.

[pilzner]

@ultrafilter

Relationships are above and beyond foreign keys exactly in the sense that they are syntactic sugar. In Access, you set up relationships by laying out graphics representing your tables in a window and drawing lines between primary keys and foreign keys. But it doesn’t do anything that an SQL CREATE TABLE statement with appropriate foreign key syntax doesn’t do.

Not working w/ access at all, I find this statement very confusing. I think your def of a relationship (correct me if wrong, but the graphic query designer that is just a shortcut for writing a query in text, and possibly the JOIN statement included in that text) is a little different than an actual data relation between two fields. It is definitely more than syntactic sugar and has very little to do with writing a query. Generally, a relation:

• Maps a foreign key to a primary key in another table
• Only has foreign key values from the available primary keys
• Can enforce the above bullet with a CONSTRAINT, though it doesn’t necessarily have to.
• Typically is marked as the two fields having the same name, or having a constraint, or both, so as to allow for easy automated documentation. Also ensures that other developers don’t have to pepper you with questions on what your intent was.

I can go into Access, or even SQL Server and write a query that joins completely unrelated fields, say IDCustomer to IDGearAssemlyLine. I may even get rows back. But on a data level, this does not make a relation.

[Ruminator]

@AHunter3

but I was expecting a little bit more structure than this!

What additional “structure” are you looking for? Let’s consider your 2 scenarios:

a)…and then define a relationship between the two like so:

TableOne::employeeserial = TableTwo::employeeserial

OK walk me through how I do that part in MySQL please?

As previously mentioned, the MySQL database has explicit syntax of CONSTRAINT FOREIGN KEY REFERENCES which is equivalent to your scenario a. But as I mentioned before, you really don’t even need to bother with this unless you want the database to enforce referential integrity or cascading deletes/updates. You can “unlearn” the desire to explicitly define a relationship – it’s just not necessary in the SQL world.

**b)**I wish to establish a second relationship between TableOne and TableTwo, such that employeeserial in TableOne equals employeeserial in TableTwo AND the field status in TableTwo does NOT equal “Inactive”.

There is no exact equivalence of this in MySQL or any other enterprise database. To approximate this functionality, you can write the logic of this “conditional” relationship in MySQL stored procedures (database triggers) and/or the frontend code (e.g. PHP) or just do it “on-the-fly” via SQL SELECT JOIN clauses.

Yes, I would benefit greatly if there were s website or a good book about SQL system written explicitly for people coming in from FileMaker. They do that for folks switching from Windows to Mac, which is not as big a leap.

IMO, the mental conversion from FileMaker to MySQL is actually a smaller leap than Win-Mac if you only consider the database engine component of FileMaker. Mental mapping of tasks is difficult because FileMaker is an out-of-the-box integration of database_engine+GUI_forms+report_writer. If you look at the strict definition of a database by computer scientists such as E.F. Codd, you’ll see no mention of fancy conditional relationships or GUI forms or reports. This “pure database” is the type of product MySQL is so it will look artificially handicapped compared to FileMaker.

The FileMaker learning map looks something more like this:

[ul]
[li]FileMaker scripting for GUI –> PHP[/li][li]FileMaker scripting for database operations –> MySQL stored procedure syntax, triggers[/li][li]FileMaker reports –> Crystal Reports[/li][li]FileMaker drag-drop development coding environment –> MS Visual Studio, SQL editor studio, or other fancy text editor[/li][/ul]

I think you already know the conceptual breakdowns I’ve listed because you mentioned earlier that you anticipated PHP code doing a lot of the “heavy lifting.”

FileMaker combines the functionality of half-a-dozen products into one. So what’s the tradeoff? The tradeoff is that FileMaker can’t handle 5 billion rows of data and its primitive locking mechanism doesn’t handle 1000 simultaneous web connections. To get the heavy duty “mission critical” database functionality, the tradeoff is to split all that integration into separate products and separate people with separate specialties (database admins, programmers, user-interface experts, etc). It does make learning it much more of a hassle.

[ultrafilter]

@pilzner

Not working w/ access at all, I find this statement very confusing. I think your def of a relationship (correct me if wrong, but the graphic query designer that is just a shortcut for writing a query in text, and possibly the JOIN statement included in that text) is a little different than an actual data relation between two fields. It is definitely more than syntactic sugar and has very little to do with writing a query. Generally, a relation:

• Maps a foreign key to a primary key in another table
• Only has foreign key values from the available primary keys
• Can enforce the above bullet with a CONSTRAINT, though it doesn’t necessarily have to.
• Typically is marked as the two fields having the same name, or having a constraint, or both, so as to allow for easy automated documentation. Also ensures that other developers don’t have to pepper you with questions on what your intent was.

I can go into Access, or even SQL Server and write a query that joins completely unrelated fields, say IDCustomer to IDGearAssemlyLine. I may even get rows back. But on a data level, this does not make a relation.

Really, I do understand what a relationship is in the ER sense of the term. That’s not what I’m talking about here–I’m talking about relationships as Access and apparently Filemaker use the word.

[Ruminator]

@pilzner

Not working w/ access at all, I find this statement very confusing.

To add to what ultrafilter said… in MS Access there is a GUI screen where you actually define and build the relationships by drawing lines from primary to foreign keys. This relationship is stored as metadata and is independent of any queries or any SELECT statements. This would somewhat equivalent of explicit relationships defined via explicit SQL syntax of CONSTRAINT FOREIGN KEY REFERENCES. If one were to run the “database documentation wizard” in MS Access, it would know about these “relationships” and print them out.

MS Access also has a similar GUI in Query Builder that lets you draw lines between primary and foreign keys that’s only active for that particular query. I’m guessing this is the scenario you’re thinking of.

[pilzner]

@Ruminator

To add to what ultrafilter said… in MS Access there is a GUI screen where you actually define and build the relationships by drawing lines from primary to foreign keys. This relationship is stored as metadata and is independent of any queries or any SELECT statements. This would somewhat equivalent of explicit relationships defined via explicit SQL syntax of CONSTRAINT FOREIGN KEY REFERENCES. If one were to run the “database documentation wizard” in MS Access, it would know about these “relationships” and print them out.

MS Access also has a similar GUI in Query Builder that lets you draw lines between primary and foreign keys that’s only active for that particular query. I’m guessing this is the scenario you’re thinking of.

Ahah, ok, this makes a lot more sense to me then. Apologies Ultrafilter, I think I got very confused as to which tool you were “drawing the lines in”. Sorry for the lecture!