Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Location
    /dev/null
    Posts
    275

    Mysql Relationship advice

    Hi all,

    I am having difficulties with some relationship issues. I am using MySQL and InnoDB.

    So let's say I have tables A, B and C + a "note" table where all comments from the 3 tables (A,B, C) are gathered. I don't have any trouble building the database and relating the different tables, but what if I want, for instance to have two rows in the note table for the same row in the Table A.
    Example, row 2 from table A has comments #3 and #4 from the note table.
    How the heck do I do that?

    I hope I ain't too confusing.

    I have added a sample sql
    Code:
    CREATE TABLE `tbl_a`
    (
    	`id` INTEGER  NOT NULL AUTO_INCREMENT,
    	`field1` VARCHAR(32),
    	`field2` VARCHAR(32),
    	`tbl_note_id` INTEGER,
    	PRIMARY KEY (`id`),
    	INDEX `tbl_a_FI_1` (`tbl_note_id`),
    	CONSTRAINT `tbl_a_FK_1`
    		FOREIGN KEY (`tbl_note_id`)
    		REFERENCES `tbl_note` (`id`)
    )Type=InnoDB;
    
    #-----------------------------------------------------------------------------
    #-- tbl_b
    #-----------------------------------------------------------------------------
    
    DROP TABLE IF EXISTS `tbl_b`;
    
    
    CREATE TABLE `tbl_b`
    (
    	`id` INTEGER  NOT NULL AUTO_INCREMENT,
    	`field3` VARCHAR(32),
    	`field4` VARCHAR(32),
    	`tbl_note_id` INTEGER,
    	PRIMARY KEY (`id`),
    	INDEX `tbl_b_FI_1` (`tbl_note_id`),
    	CONSTRAINT `tbl_b_FK_1`
    		FOREIGN KEY (`tbl_note_id`)
    		REFERENCES `tbl_note` (`id`)
    )Type=InnoDB;
    
    #-----------------------------------------------------------------------------
    #-- tbl_c
    #-----------------------------------------------------------------------------
    
    DROP TABLE IF EXISTS `tbl_c`;
    
    
    CREATE TABLE `tbl_c`
    (
    	`id` INTEGER  NOT NULL AUTO_INCREMENT,
    	`field5` VARCHAR(32),
    	`field6` VARCHAR(32),
    	`tbl_note_id` INTEGER,
    	PRIMARY KEY (`id`),
    	INDEX `tbl_c_FI_1` (`tbl_note_id`),
    	CONSTRAINT `tbl_c_FK_1`
    		FOREIGN KEY (`tbl_note_id`)
    		REFERENCES `tbl_note` (`id`)
    )Type=InnoDB;
    
    #-----------------------------------------------------------------------------
    #-- tbl_note
    #-----------------------------------------------------------------------------
    
    DROP TABLE IF EXISTS `tbl_note`;
    
    
    CREATE TABLE `tbl_note`
    (
    	`id` INTEGER  NOT NULL AUTO_INCREMENT,
    	`note` TEXT,
    	PRIMARY KEY (`id`)
    )Type=InnoDB;

  2. #2
    Join Date
    Mar 2009
    Location
    Gods Own Country
    Posts
    681
    If possible redesign the database properly.. i.e. use three additional tables for notes.. or you can go with a nasty idea of generating ids like a_1, b_3, c_4 where a_1 represents row #1 in table a, like that..

  3. #3
    Join Date
    May 2009
    Posts
    766
    You don't necessary need three notes tables...you could just have something like this:

    create table tbl_note
    (
    id INTEGER NOT NULL AUTO_INCREMENT
    table CHAR(1) // A, B, or C
    table_key INTEGER
    primary key (id)
    )Type=InnoDB;

    To get multiple comments for one row in table a, you would do

    SELECT id,note FROM tbl_note WHERE table='A' and table_key=2

  4. #4
    Join Date
    May 2008
    Location
    Rutherford, NJ
    Posts
    68
    the proper way would be to make a note table for each table. or find a way to combine and relate the three tables, assuming the data works that way.
    Database design and performance optimization, custom PHP scripts, and publicly available resources for developers!
    http://www.zeropride.com

  5. #5
    Join Date
    Nov 2001
    Location
    Vancouver
    Posts
    2,416
    I agree with mattle, based on the requirement stated by the OP, there is no need to have a separate "note" table for each of the other (A, B, C) tables; if you are going to do that you may as well add the Note column to the tables in question.

    Code:
    (1) Table Person
    1 George
    2 Paul
    3 Ringo
    
    (2) Table Address
    1 123 Main Street
    2 1800 Pennsylvania Drive
    
    (3) Table Organization
    1 Coin Collectors Guild of Lichtenstein
    2 Aardvarks Anonymous
    
    ID Table Note
    1  2     Deliveries at the rear
    2  1     Likes chocolate
    3  1     Is married to Gwen
    4  3     Founded by Elmer Fudd
    (Note that "address" might be a data element you wish to treat like "Note" - i.e. addresses can be found attached to people, places, organizations - just like notes might be related to many tables in a solution's schema.)
    “Even those who arrange and design shrubberies are under
    considerable economic stress at this period in history.”

  6. #6
    Join Date
    Mar 2009
    Posts
    2,218
    Is a note ever applicable to more than one row in table A, B, or C?

    If it is, you need to create an extra table; each row of that table will identify a row in column A, B, or C, and a row in the notes table.

    So, for example, if note 7 applies to row 12 in table A, and row 15 in table B, and note 42 applies to row 15 in table B, rows in the new table will contain the following:


    7 A 12
    7 B 15
    42 B 15

  7. #7
    Join Date
    May 2009
    Posts
    766
    I believe the OP had a number of one-to-many relationships where all of the "many's" have an identical format and therefore can be lumped together into one table referencing the primary key and the table to which the pk belongs.

    If it is indeed a collection of many-to-many relationships, then I would go with your solution of a three-field bridge table. It should be noted that in that case, the table that actually contained the notes would simply be a primary key and the note contents (no need to reference foreign keys anymore).

  8. #8
    Join Date
    Mar 2009
    Posts
    2,218
    Maybe we can make this clearer.

    In his design, the OP has a table (actually three) which contain a column, which contains a pointer to the notes table.

    The OP has discovered that this means each row of table A can only refer to one note. And this does not meet the requirements.

    So, the design is wrong.

    Instead of a column in table A which refers to a row in the notes table, what's needed is a way for each note to be related to a table and row it is related to.

    If each note can refer only to a single row in a single table, the following design is fine:

    table notes:

    ID
    table-referred_to
    row-referred_to
    note_text

    ...

    and delete the references to notes from tables A, B, and C.

    Then you can select from table notes all rows which refer to row x of table y.

    However, if a note's text is applicable to more than row, the above is inadequate: it runs into the same problem the OP already has.

    By the way, this actually is the basis of relational database design. I guess because I was around when this weas being developed into commercial reality, I read the books at the time. CJ Date's book "Introduction to Database Systems" was highly regarded ... (Date was on the staff at IBM, which invented relational databases.)

    But I've just been staggered by the price on Amazon. (And anyway it also covered non-relational database systems, which are probably of near-zero interest to most people these days.)

    So: can anyone recommend a lower-cost book that explains the principles of relational database design? It's really not that hard.

Similar Threads

  1. MYSQL relationship
    By cilina in forum Programming Discussion
    Replies: 2
    Last Post: 03-22-2008, 06:06 AM
  2. VPS + Mysql advice
    By Mize in forum VPS Hosting
    Replies: 6
    Last Post: 02-28-2007, 12:48 PM
  3. MySQL / PHP Overloads, please some advice!?
    By Matrix_Neo in forum Hosting Security and Technology
    Replies: 3
    Last Post: 04-20-2005, 05:30 PM
  4. PHP/MYSQL Expert Needed - Long Term Relationship
    By mouschi in forum Employment / Job Offers
    Replies: 5
    Last Post: 06-25-2004, 11:06 PM
  5. Need a dedivated or vps for MYSQL any advice or help
    By bacanak in forum Dedicated Server
    Replies: 1
    Last Post: 03-27-2004, 04:15 AM

Posting Permissions

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