On 2021-06-21 12:07, JORGE MALDONADO wrote:
Hi,
I have the following tables: reports, standards, elements, tables, items.
* One report has many standards.
* One standard has many elements.
* One element has many tables.
* One table has many items.
Reports are generated yearly and current year might have exactly the same standards as last year but not necessarily. Maybe, the current year report includes part of last year's report standards and also includes new standards. Or maybe, the current year report includes only a subset of last year reports standards. Or maybe, the current report has a totally new set of standards.The same behavior is true between standards and elements, between elements and tables and between tables and items. I see that many-to-many relations exist between all of the tables but I am not able to clearly visualize.
I am a bit confused about how to design this part of my DB. I hope my explanation is clear enough. I will very much appreciate your feedback.
Respectfully, Jorge Maldonado
First of all, get rid of the name "
tables" as one of your table names. It will cause nothing but confusion for you & the people trying to help you. In the following discussion, I'm going to replace the name "
tables" with "
groups".
Think of this of this in two parts:
- What describes each table.
- How the data will describe the relationships between the tables.
Start off with the
items: This table names columns for the information about each
item, including a (probably) unique column for each
item. Each data row (record) in this table will describe the details of each
item.
Now define
two tables for your groups. The first table names columns for the information associated with each
group, including a (probably
) unique column for each
group. Each data row in this table will describe the details for each
group. The second table has typically just a column for a
group & an
item that the
group contains. Each data row in this table will simply list, for each
group name, all the
items (by name) that it contains, one row for each
group/
item pair. This second table might be named "group_items" to distinguish it from the "
groups" table name.
Repeat the above process for
groups, for each
element,
standard, &
report.
If standards change for each year (& this applies to all of your tables), you are going to have to represent that in the table
data,
not in the table design. Eg, you may want names (
in the data, not the table description) for standards for each year, with a year prefix or suffix in the name.
A more sophisticated approach might have a year column in the relevant tables, but it's important to get started with the basics, & then make changes as you see fit. That's one of the beauties of SQL databases: Once you come up with a better design, it's trivial to then create the data in the new tables with simple SELECT statements from the old tables.
Also, try to find a good book on database design. No, I don't know of any.