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

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.

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.

One example is worth a thousand words:

CREATE TABLE "Items" (
    item_name VARCHAR,
    item_stuff VARCHAR
);
--------------------------------
CREATE TABLE "Groups" (
    group_name VARCHAR,
    group_stuff VARCHAR
);

CREATE TABLE "GroupItems" (
    group_name VARCHAR,
    item_name VARCHAR
);
--------------------------------
CREATE TABLE "Elements" (
    element_name VARCHAR,
    element_stuff VARCHAR
);

CREATE TABLE "ElementGroups" (
    element_name VARCHAR,
    group_name VARCHAR
);
--------------------------------
CREATE TABLE "Standards" (
    standard_name VARCHAR,
    standard_stuff VARCHAR
);

CREATE TABLE "StandardElements" (
    standard_name VARCHAR,
    element_name VARCHAR
);
--------------------------------
CREATE TABLE "Reports" (
    report_name VARCHAR,
    report_stuff VARCHAR
);

CREATE TABLE "ReportStandards" (
    report_name VARCHAR,
    standard_name VARCHAR
);
--------------------------------
Obviously, the *_stuff column is replaced by table-specific columns.