Re: Help to design relations between some 5 tables with possible many-to-many relations

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:

1.  What describes each table.
2. *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.