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

flying_donkey · #1 (**permalink**) 10-23-09, 03:10

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'

healdem · #2 (**permalink**) 10-23-09, 03:22

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

flying_donkey · #3 (**permalink**) 10-23-09, 12:37

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.

healdem · #4 (**permalink**) 10-23-09, 12:57

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

Internet Services

Web Development

Digital Marketing

Consumer Tech