A form including content from two tables

Question

I am working to create a database of all the places or administrative entities of one country.

Most work will be about adding new districts. I have to detail which region every district belongs to.

Two tables, one for 1/ regions 2/ districts. Every region has its ID and Name. Every district has a Name, and a column which refers to ID_Region (the one it belongs to). The two tables are linked. ID_Region is primary key.

However, I am using a well-known open-source Database management system, (OpenOffice). It seems that it cannot allow me to create forms in which ID_Region could be automatically modified in Name_Region. This is all the more important since there are hundreds of regions.

1. Do you know if this is possible on any open-source DBMS?
2. What software should I use instead?

You probably got that I'm a database newbie. I do not know much about SQL. Thanks for your replies. Feel free to redirect me to the right forum if I am on the wrong one.

---

• Table 1: "Regions"

  • Column 1: idRegions
  • Column 2: RegionType (refers to another Table, not necessary here)
  • Column 3: Name
  • Column 4: Abbreviation

• Table 2: "Districts"

  • Column 1: idDistricts
  • Column 2: Name
  • Column 3: Region (tied to Regions.idRegions)

I can create a form in which I can view and add Districts, but it seems like Districts.Region (an ID) can't "redirect" to Region.Name, which I would like to (no matter how: a drop-down list, or an additional text box).

Should this be possible, it would of course allow other types of join to show names and not id (such as RegionType, or any kind of join whatsoever).

You might think of making Region.Name the Primary key instead of using IDs, however, some Regions are called the same way (all the more when I will add the towns, many Towns' Districts are homonyms).

Answer 1

The way you have the database setup seems fine. I am not sure this is actually a DB admin question as much as it is a front end question.

MySQL or MariaDB would be a fantastic DB to use this on. You can also use MySQL Workbench, which is free, to manage the DB if you want. I think for something this simple, I would just use the command line MySQL client.

If this is truly a front end question about how to make a form, I would recommend using the PDO functionality in PHP.

Once you decide this for sure, just ask about the proper queries to write and how to use PDO, if you need to. (That might be more appropriate for Stack Overflow, though)