SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    www.devshed.com/server_side/mysql/normal/normal2

    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.
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "references" is ignored in MySQL -- so it's not doing anything. MySQL just allows it for compatibility with other REAL DBs.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  4. #4
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •