One to many mySQL database setup

Question

I'm having trouble wrapping my head around how to set this up. I have two tables:

• Projects
• Sections

1 project can belong to many sections.
1 section can have many projects.

1. How would I go about setting this up, would this be a many to many relationship or one to many?
2. Would it best if I created a lookup table?

Answer 1

This is a simple 1-to-many relationship . The 2 requirements you stated above amount to the same thing. No lookup table is required. In your sections table you simply have a fk back to the projects table

Answer 2

Create a relationship table... let's call it ProjectSections, with fields ProjectId and SectionId. You create the relationship between a project and a section in ProjectSections by creating a record with the ProjectId and the SectionId.

ProjectSections then links the Projects and Sections together.

Answer 3

would this be a many to many relationship or one to many?

It's regarded as many-to-many, because many projects can refer to many sections.

You'll need to implement a table that sits between projects and sections, using foreign keys from both to be the primary key. Example:

PROJECTS_SECTIONS_XREF table

• PROJECT_ID, pk, fk
• SECTION_ID, pk, fk

The naming convention is up to you, but I recommend something informational by using the names of both tables involved. Corrollary, cross references (xref), lookup, etc. There's been two questions regarding the naming convention in the past week or so - all the names are synonyms, choose whatever you like.