Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Unanswered: One row of table 'a' refer to many rows of table 'b'

    I have 2 tables, 'a' and 'b'

    On the 'a' table I want each row to refer (have the id's) to any 1 to 50 rows of table 'b'

    What is the best way to make table 'a' for this ?

    I have to create 50 columns with one id each collumn or there are other ways ?
    If this is, what type have to be the 'a' columns ?
    (I heard something about joint, foreign keys etc but I dont know if anything of this is for my case)

    *any rows of 'a' can refer to the same row of 'b'

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its called using a foreign key
    the datatype in each table should be the same
    Code:
    table A (persons)
    ID              // primary key
    title
    forenames
    surnames
    ....... //and so on
    
    table B (addresstypes)
    ID             //primary key
    Description
    
    table C (addresses)
    personID //foreign key to ID in persons               }
    AddrerssTypeID //foreign key in addresstypes          } primary key 
    line1
    line2
    ....... //and so on
    thsi design only allows for one addrress type for each person. say if you wanted to have multiple work addresses you woudl need to either define multiple work addresses in addresstypes or remove the addresstypeid from the priamry key in table c and add somethign else to make the address unique
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2009
    Posts
    2
    Thanks healdem, Ill try to explain better what is my problem, I have 2 tables:
    Code:
    table A (project)
    projectID
    projectName
    projectType
    ...
    
    
    table B (person)
    personID
    forename
    surname
    tool1
    tool2
    tool3
    phone
    ...
    now the situation is :
    Persons participate in projects. A person uses one tool in a project. Many persons (up to 50) can participate in one project. In addition one person can participate in an unlimited number of (existing) projects.

    So what I want is to know what persons participate in witch projects, and what tools each person used in each project.

    I assume the obvious way is to add 50 columns (foreign keys) in table A to refer each, in one personsID, and another 50 to refer to the corresponding collumn of table B witch is the tool used by each person for each project. I am mysql (and database) newbie so I thing probably there are some other ways/ideas/advises about this. I have to use php to control the database.
    My concerns are database extendability, and query execution speed.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    whenever you see repeating groups like tool1, tools, tool3.. tooln that's a sure sign of bad design, designnormalisation issues

    so as I see it you have 3 strng entiies (projects, people and tools), you need a way of making a many to many realtionship, normally in the physical world this is done as an intersection table where you store the only itnes relevant to that intersection of 2 entities

    eg table tools
    ID
    description

    peoplestools
    PersonID 'fk to personid in persons
    ProjectID 'fk to ID in oprojects
    toolID 'fk to ID in tools
    datefrom
    dateto

    the PK in Personstools is a composite of person and tool, this would work if the person only used the tool once for that project, or only used the tool once contiguously for the duration of the project. if you needed to record more than one use of the same tool on the same project then you need to use soemthign else to make the tool use unique
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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