Seeking advice on MySQL backend architecture

[system]

@atlr

Hey Kalon, OP, etc

I’ve just read this thread and, while my query is * slightly * off-topic, I was just wondering if I could just interject and ask you, Kalon, what a Foreign Key is, and how one can link to / use this, to make a database relational?

I suspect this is a really ‘duh’ question, for which apologies, but I’m trying to do something (see thread: http://www.sitepoint.com/forums/showthread.php?t=712186) and I’m wondering whether this “foreign key” / “primary key” thing might help me out too!

Thanks in advance, and sorry for butting-in.

Alex

A foreign key is basically a column or group of columns in one table whose values must exist in a corresponding column or groups of columns in another table.

A simplistic example:

tblCustomers

fldCustomerId - PK
fldFname
fldLname

tblAccounts

fldAccountNo
fldCustomerId - FK
fldAccountType

You can set fldCustomerId in tblAccounts to be a foreign key linking to fldCustomerId in tblCustomers. This means that if you try to enter a record in tblAccounts with a fldCustomerId that does not already exist in tblCustomers you will get an error. After all, in real life you cannot have an account that does not belong to a customer.

Regarding your properties table in your other thread, all you need is an additional column called fldFeatured or something similar and set it to a boolean, 0 or 1, yes or no, on or off, or whatever via a checkbox in a form.

Then on your home page, query the properties table and retrieve the records whose fldFeatured is set to “yes” and then display those properties.