Joining vs referencing
I have been reading a lot of db normalization.
The question I have concerns mysql and joining and refrencing.
I realize that you can join a table to allow refrencing in other tables however I have just been reading through the devshed articals on normalization.
In this artical they use when creating the table a reference attrib so one column would be
category_id smallint unsigned not null references categories
categories being the other table. From this point forward you can reference anywhere within the multiple tables without any table joins.
I was wondering why this isn't a technique that is shown more often, or if I am interpreting the results incorrectly? This would seem to be a much easier and more efficient way to allow data portability throughout the db layout. :)
"references" is ignored in MySQL -- so it's not doing anything. :) MySQL just allows it for compatibility with other REAL DBs.
Well that sucks. It would have made things so much easier. I wish Mysql would support more of the things I have been reading about. :)
The REFERENCES keyword just creates the FOREIGN KEY CONSTRAINT. The FK constraint just forces the dbms to apply the constraint for any inserts/updates/deletes.
If you want data from multiple tables you still need to JOIN them.