agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedHelp to design relations between some 5 tables with possible many-to-many relations
4+ messages / 3 participants
[nested] [flat]
* Help to design relations between some 5 tables with possible many-to-many relations
@ 2021-06-21 19:07 JORGE MALDONADO <jorgemal1960@gmail.com>
2021-06-21 19:25 ` Re: Help to design relations between some 5 tables with possible many-to-many relations David G. Johnston <david.g.johnston@gmail.com>
2021-06-21 21:03 ` Re: Help to design relations between some 5 tables with possible many-to-many relations Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 2 replies; 4+ messages in thread
From: JORGE MALDONADO @ 2021-06-21 19:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help to design relations between some 5 tables with possible many-to-many relations
2021-06-21 19:07 Help to design relations between some 5 tables with possible many-to-many relations JORGE MALDONADO <jorgemal1960@gmail.com>
@ 2021-06-21 19:25 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2021-06-21 19:25 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
On Mon, Jun 21, 2021 at 12:07 PM JORGE MALDONADO <jorgemal1960@gmail.com>
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*.
> [...]
> I see that many-to-many relations exist between all of the tables but I am
> not able to clearly visualize.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help to design relations between some 5 tables with possible many-to-many relations
2021-06-21 19:07 Help to design relations between some 5 tables with possible many-to-many relations JORGE MALDONADO <jorgemal1960@gmail.com>
@ 2021-06-21 21:03 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-21 21:57 ` Re: Help to design relations between some 5 tables with possible many-to-many relations Dean Gibson (DB Administrator) <postgresql@mailpen.com>
1 sibling, 1 reply; 4+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-06-21 21:03 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help to design relations between some 5 tables with possible many-to-many relations
2021-06-21 19:07 Help to design relations between some 5 tables with possible many-to-many relations JORGE MALDONADO <jorgemal1960@gmail.com>
2021-06-21 21:03 ` Re: Help to design relations between some 5 tables with possible many-to-many relations Dean Gibson (DB Administrator) <postgresql@mailpen.com>
@ 2021-06-21 21:57 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
0 siblings, 0 replies; 4+ messages in thread
From: Dean Gibson (DB Administrator) @ 2021-06-21 21:57 UTC (permalink / raw)
To: pgsql-sql
On 2021-06-21 14:03, Dean Gibson (DB Administrator) wrote:
>
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-06-21 21:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-21 19:07 Help to design relations between some 5 tables with possible many-to-many relations JORGE MALDONADO <jorgemal1960@gmail.com>
2021-06-21 19:25 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-21 21:03 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
2021-06-21 21:57 ` Dean Gibson (DB Administrator) <postgresql@mailpen.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox