Creating one-many relationship between street sections and car scans?-CodePudding

I have two tables and need to create a one to many relationship between them. tlbsection represents a series of street sections as lines in a city. Each street section has its own id. tlbscans represents a on street scan of a street section counting cars on it. I need to relate tlbscans to tlbsection as a street section and can have more than one scan. What is a good way to do this with with the example data below?

tlbsections ID(PK) | geom | section |

1 | xy | 5713 |

2 | xy | 5717 |

tlbscans section | a | b |

5713 | 30 | 19 |

5717 | 2 | 1 |

CodePudding user response：

The overwhelming question: Is the column section unique in tlbsections. If it is then create a unique constraint on it. Then create a FK on column section in table `tblscans' referencing. Assuming the tables already exist:

alter table tlbsections 
      add constraint section_uk
          unique section; 

alter table tblscans
      add constraint scans_section_fk 
          foreign key (section) 
          references tlbsections(section);

If column section unique in tlbsections is not unique then you cannot build a relationship as currently defined. Without much more detail, I suggest you add a column to contain tlbsections.id, create a FK on the new column then drop column section tblscans.

alter table tblscans
      add tlbsections_id <data type>; 
   
alter table tblscans
      add constraint sections_fk 
          foreign key (tlbsections_id) 
          references tlbsections(id);

alter table tblscans
      drop column  section;

There may be other options, but not apparent what is provided.