database design – Object with many-to-many relationships with multiple tables

Here’s an example for a use-case that matches what I’m trying to better understand. Say I have 3 objects I need to deal with that are similar in many ways, such as invoice, PO, and receipt. They can all have a number, and amount, etc. They can also have many similar relationships, such as line items, images attached, etc. I can think of a number of ways of modeling this.

Option 1
Make everything separate. Have tables for invoice, invoice_line, invoice_image, then po, po_line, po_image, etc. This means strong referential integrity with foreign keys and least possible number of joins, but a ton of duplication for each table.

Option 2
Have parent document table with common fields and a type field, then have invoice, po, and receipt table have a foreign key to document_id. I can then have a single document_image table. For the lines, there are again some differences but many similarities between all, so could have a document_line table with a foreign key to document_id and invoice_line, po_line, and receipt_line tables with foreign key to document_id. Here, we have less duplication, keep referential integrity with foreign keys, but start having many more joins to get all the info we need. If I have an invoice line item and wanted to get all the info, I’d need to join invoice_line to document_line, invoice, and document.

Option 3
Use separate invoice/po/receipt tables, but for image relationship (or any other) add multiple nullable foreign keys, so image would have invoice_id, po_id, and receipt_id nullable foreign keys. With this we can still enforce referential integrity but we have some fields that will often be useless polluting things, plus we now can’t make a necessary field required because they all need to be nullable. We do cut down on duplication.

Option 4
Use separate invoice/po/receipt tables, but for image relationship (or any other) have a type field and fk_id field. This way I don’t need to have multiple many-to-many tables so it cuts down on duplication, especially if you have lots of these many-to-many, since it would always be 3 tables each time. I like this option the least because you can’t have a foreign key, I pretty much not even considering it as a valid option.

I’m leaning towards option 1 or 2, I think options 3 or 4 seem like bad design and I’d likely only consider if somebody explained that there are major performance benefits. For option 1, even though there’s duplication, you can get around it with code generation, so maybe not so big a deal. But would be interested in knowing if there’s a major advantage to breaking it down like option 2.

MySQL Select Data from 5 Different Tables

I have to select data from 5 different tables. The main table is `Notices`. Following are the rows from the `Table Notices` which I want to select

SELECT * FROM `notices` WHERE notices.publication_date >’2020-03-01′ and notices.publication_date < ‘2020-03-31’

Now I also have to select related information for these rows from different tables. Some tables have a direct relation with Table Notice and some tables are related via other tables.

My expected output is the following. Where I will have to take `Company Name`, `SIC Code` from `Table Companies` ; `SIC Code Description` from `Table Sic_Codes`; `Prac.Name`, `Prac.Company`, `Prac.Phone` from `Table Insovency_Practionar`

<pre>
+——-+————-+——————+—————–+————–+———-+————————–+————+————–+————+
| ID | Notice Code | Publication_Date | Company_Number | Company Name | SIC Code | Sic Code Description | Prac. Name | Prac.company | Prac_Phone |
+——-+————-+——————+—————–+————–+———-+————————–+————+————–+————+
| 96008 | 2410 | 2020-03-02 | 09844265 | ATL Logistic | 49410 | Freight Transport | Mr. Conard | Conard Ltd | 01234567 |
| 96014 | 2410 | 2020-03-02 | 02640968 | New-Tonne | 28220 | Manufacturer Lifting… | Mr. Andrew | Andrew Ltd | 03243434 |
| 96032 | 2410 | 2020-03-02 | 03666759 | Sonataine | 41100 | Development and Building | Mr. Mark | Mark Ltd | 038743287 |
+——-+————-+——————+—————–+————–+———-+————————–+————+————–+————+

</pre>

Below I have described the relation between each Table with `Table Notices`

`Table Companies` has a direct relation with `Table Notices`

<pre>
Table: Companies

Companies.Company_number = Notices. Company_ Number
+——-+————–+—————–+————–+———-+——-+
| ID | Company_Name | Company_Number | Address | postcode | Sic1 |
+——-+————–+—————–+————–+———-+——-+
| 81983 | ATL Logistic | 09844265 | Scot Street | DY4 7AG | 49410 |
| 81989 | New-Tonne | 02640968 | Withins Road | WA11 9UD | 28220 |
| 82004 | Sonataine | 03666759 | Vastry Road | TN14 5EL | 41100 |
+——-+————–+—————–+————–+———-+——-+
</pre>

`Table Sic_codes` doesn’t have a direct relation with `Table Notices`. But it has with `Table Companies`.
<pre>
Table: Sic_Codes.
Companies.Sic1 = Sic_code.Code

+—–+——-+————————–+————————+—————-+
| ID | Code | Description | Division | Section |
+—–+——-+————————–+————————+—————-+
| 468 | 49410 | Freight Transport | Land Transport | Transportation |
| 262 | 28220 | Manufacture of Lifting… | Machinery and Eqipment | Manufacture |
| 334 | 41100 | Development and Building | Construction Building | Construction |
+—–+——-+————————–+————————+—————-+
</pre>

`Table Insovency_Practionar` does not have a direct relation with `Table Notices`. There is another `Table Notice_insolvency_practitionar_ID` to create relation between these two tables `Table Insovency_Practionar and Table Notices`

<pre>
Table: Notice_insolvency_practitionar_ID .

Notice_insolvency_practitionar_ID. Notice_ID = Notices. ID
+———–+—————————–+
| Notice_ID | Insolvency_Practiotionar_ID |
+———–+—————————–+
| 96008 | 1048 |
| 96014 | 725 |
| 96032 | 548 |
+———–+—————————–+
</pre>

I have to use the above table to fetch relevant rows from the below table.

<pre>
Table: Insovency_Practionar .

Insovency_Practionar.ID = Notice_insolvency_practitionar_ID. Insolvency_Practiotionar_ID
+——+————+————+————+———–+——————-+
| ID | Name | Company | Address | Phone | Email |
+——+————+————+————+———–+——————-+
| 1048 | Mr. Conard | Conard Ltd | Birmingham | 01234567 | |
| 725 | Mr. Andrew | Andrew Ltd | New Road | 03243434 | |
| 548 | Mr. Mark | Mark Ltd | Hamilton | 038743287 | |
+——+————+————+————+———–+——————-+

</pre>

My expected Output

<pre>
+——-+————-+——————+—————–+————–+———-+————————–+————+————–+————+
| ID | Notice Code | Publication_Date | Company_Number | Company Name | SIC Code | Sic Code Description | Prac. Name | Prac.company | Prac_Phone |
+——-+————-+——————+—————–+————–+———-+————————–+————+————–+————+
| 96008 | 2410 | 2020-03-02 | 09844265 | ATL Logistic | 49410 | Freight Transport | Mr. Conard | Conard Ltd | 01234567 |
| 96014 | 2410 | 2020-03-02 | 02640968 | New-Tonne | 28220 | Manufacturer Lifting… | Mr. Andrew | Andrew Ltd | 03243434 |
| 96032 | 2410 | 2020-03-02 | 03666759 | Sonataine | 41100 | Development and Building | Mr. Mark | Mark Ltd | 038743287 |
+——-+————-+——————+—————–+————–+———-+————————–+————+————–+————+

</pre>

How to join these tables to select the data with their relevant information.

I have used the following query. But I am not entirely sure. Can you please help ?
SEMrush

SELECT n.id
, n.gazette_notice_id, n.notice_code, n.company_number, n.publication_date, c.company_name, c.registered_address_town, c.registered_address_postcode, c.sic_1, s.description, s.division, s.section, i.name practionar_name, i.company practitioner_company, i.address prac_address, i.phone
FROM notices n
LEFT
JOIN companies c
ON c.company_number = n.company_number
LEFT
JOIN sic_codes
ON s.code = c.sic_1
LEFT
JOIN notice_insolvency_practitioners ni
ON ni.notice_id = n.id
LEFT
JOIN insolvency_practitioners i
ON i.id = ni.insolvency_practitioner_id
WHERE n.publication_date >’2020-05-01′
AND n.publication_date <‘2020-05-31’

Thanks in advance.

 

database – How to improve wordpress mysql performance on large tables?

I’ve installed WordPress 5.4.1 on Ubuntu 20.04 LTS on AWS EC2 (free tier as I’m starting).

My instance has 30 GB of disk space and 1 GB of RAM.

My website has at about 9000 pages and I’ve imported 7800 so far with “HTML Import 2” plugin.

wp_posts table has 7,800 rows and 66 MB size and, since this table has grown, wordpress has become super slow. Any change I make to the database is super slow as well.

While trying to make changes, I keep getting this error:

Warning: mysqli_real_connect(): (HY000/2002): No such file or directory in
/var/www/wordpress/wp-includes/wp-db.php on line 1626 No such file or
directory

Error reconnecting to the database This means that we lost contact
with the database server at localhost. This could mean your host’s
database server is down.

What could I do in order to achieve a better speed and make it usable?

postgresql – Count on has_many associated records only if the has_many tables have two records with given names

I have the main table on which I have to apply count. I have two bridge tables and two nested tables for has_many relation. You can generate tables and bridge tables with below code and also insert some data

CREATE TABLE main_table
(
    id        serial PRIMARY KEY,
    is_active BOOLEAN,
    name      VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE nested_table_1
(
    id   serial PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE nested_table_2
(
    id   serial PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE bridge_table_1
(
    main_table_id     INTEGER REFERENCES main_table (id),
    nested_table_1_id INTEGER REFERENCES nested_table_1 (id)
);

CREATE TABLE bridge_table_2
(
    main_table_id     INTEGER REFERENCES main_table (id),
    nested_table_2_id INTEGER REFERENCES nested_table_2 (id)
);


INSERT INTO main_table (is_active, name)
VALUES (true, 'main 1');
INSERT INTO main_table (is_active, name)
VALUES (true, 'main 2');

INSERT INTO nested_table_1 (name)
VALUES ('Contact');
INSERT INTO nested_table_1 (name)
VALUES ('Dropley');

INSERT INTO nested_table_2 (name)
VALUES ('nested table second 1');
INSERT INTO nested_table_2 (name)
VALUES ('nested table second 2');
INSERT INTO nested_table_2 (name)
VALUES ('nested table second 3');
INSERT INTO nested_table_2 (name)
VALUES ('Drop');

Query

SELECT COUNT(main.id)
      FILTER (where nes_2.name IN ('nested table second 1', 'nested table second 2') AND
                    nes_1.name = 'Contact')                              AS contact_compliants,
      COUNT(main.id)
      FILTER (where nes_2.name = 'Drop' AND nes_1.name = 'Dropley')      AS droplet_compliant,
      COUNT(main.id),
      COUNT(main.id) FILTER (where nes_2.name IS NOT NULL AND
                                    nes_1.name IN ('Contact', 'Droplet')) AS total_opportunities
FROM main_table AS main
        INNER JOIN bridge_table_1 AS bt_1
                    ON bt_1.main_table_id = main.id
        INNER JOIN nested_table_1 AS nes_1
                    ON bt_1.nested_table_1_id = nes_1.id
        INNER JOIN bridge_table_2 AS bt_2
                    ON bt_2.main_table_id = main.id
        INNER JOIN nested_table_2 AS nes_2
                    ON nes_2.id = bt_2.nested_table_2_id
WHERE main.is_active = 'true';

The query above is working all great counting everything fine. Just one issue. In the first part for contact_compliants, it has IN which means either one of the names for nested will increment count. which is not my requirement. I want to count only when when both the names matches.

to be clear: How do I make this part where nes_2.name IN (‘nested table second 1’, ‘nested table second 2’) to count only when nes_2.name = ‘nested table second 1’ AND nes_2.name = ‘nested table second 2’). I have tried adding double join so that I can compare but no success. I have also seen similar solution but I am not sure, how it will apply here with Having

postgresql – Postgres – Insert data into view – tables have many to many relationship

After a day of banging my head against my desk I thought I’d reach out for help. Any points in the right direction would be much appreciated.

In Postgres, I want to insert data into a view, “hosts” that links to tables with a many to many relationship, “nodes” and “groups” with the linking IDs in “many_groups_has_many_nodes”.

Since that will not work, I call a trigger to ‘INSTEAD OF INSERT’ and execute a function, “update_hosts_entries”.

As a part of that function I want to pass in a text array from my original insert to the view.
For each value in that array, I want to add that value into my “groups” table as a new “group name” if it does not already exist as well as add an association in the “many_groups_has_many_nodes” table.

View

CREATE VIEW "hosts" AS
SELECT
  public.nodes.n_name AS name,
  public.nodes.n_hostname AS hostname,
  array_agg(public.groups.g_name) AS groups
FROM
  public.many_groups_has_many_nodes
  INNER JOIN public.groups ON (public.many_groups_has_many_nodes.id_groups = public.groups.id)
  INNER JOIN public.nodes ON (public.many_groups_has_many_nodes.id_nodes = public.nodes.id)
GROUP BY
  n_name,
  n_hostname

INSERT

INSERT INTO "hosts" ("name", "hostname", "groups")
VALUES ('myrouter', '10.10.10.10','{default,west-coast}');

TRIGGER

CREATE TRIGGER update_hosts_entries_trigger
  INSTEAD OF INSERT ON hosts for each row EXECUTE procedure update_hosts_entries();

FUNCTION

Create or replace function spInsertIntoGroups (
p_NodeName text(50),
p_GroupName text())

Returns void as $$

     DECLARE
     v_NodeID int;
     v_GroupID int;

     BEGIN
     Select id into v_NodeID from nodes where n_name = p_NodeName;
     Select id into v_GroupID from groups where g_name = p_GroupName;

     If (v_NodeID is null)
     Then
          Insert into nodes values(p_NodeName);
          v_NodeID := SCOPE_IDENTITY();
     End if;

     If (v_GroupID is null)
     Then
          Insert into groups values(p_GroupName);
          v_GroupID := SCOPE_IDENTITY();
     End if;

     Insert into many_groups_has_many_nodes values(v_NodeID, v_GroupID);

End;
$$ LANGUAGE plpgsql;

I believe I am getting close but am stumped on what to do (or search for) in regards to the function.

Any help is much appreciated.

sql server – Foreign keys to primary tables or nested table

In this hypothetical example should the foreign key constraint setup for the ProductId and UserId columns in the ProductUserCommentAction table be referencing the Product/User tables as shown in the first diagram OR is it OK for those columns to reference the ProductUserComment table as shown in the second diagram?

I like how it’s setup in the second diagram as it reduces the spider web in visualizations.

Are there any downsides to this second approach?

enter image description here

Versus

enter image description here

Linking Some Tables

I have three tables :

Product
———
Product ID key
image
Description

Order Item
————
Order ID key
Product ID fkey
Quantity
SEMrush

Saved Orders (a record of bought transactions)
—————
SavedOrderID key
Name
Date
Product ID fkey
Quantity

The order item reduces its quantity when an order is made. If all of the item is bought than the order item record is deleted.

My question are:

1. Does the link and columns look okay for the three tables?
2. Should Saved Orders be a separate database?
3. If there is some database where a table such as Customers can link to more than one column what is done?

Thanks a ton,
Joshua

 

magento2 – Megento 2 Left joining two tables, if null default to 0

Title self esplicative, but when I perform the join, then add field to select then make the expression I get an error.

$this->getCollection()->getSelect()
        ->joinLeft(
            (
                "transaction" => $this->getCollection()->getTable("transaction_table")
            ),
            "(transaction.supplier_id = main_table.supplier_id)",
            (
                'transaction_id' => 'entity_id',
                'amount' => 'amount',
                'date' => 'date'
            )
        )
        ->joinInner(
            (
                'supplier' => $this->getCollection()->getTable('supplier_table')
            ),
            "(supplier.entity_id = main_table.supplier_id)",
            (
                'business_name' => 'business_name'
            )
        );
$this->getCollection()
        ->addFieldToSelect(new Zend_Db_Expr("CASE WHEN amount is NULL THEN 0 ELSE amount END AS amount_sum"))
        ->addFieldToSelect(new Zend_Db_Expr("CASE WHEN date is NULL THEN main_table.start_date ELSE date END AS date"));
$this->getCollection()->getSelect()
        ->columns(('current_amount' => new Zend_Db_Expr('SUM(amount_sum)')))
        ->group('main_table.entity_id');

But I get the following error:

SQLSTATE(42S22): Column not found: 1054 Unknown column 'amount_sum' in 'field list', query was: SELECT CASE WHEN amount is NULL THEN 0 ELSE amount END AS amount_sum, CASE WHEN date is NULL THEN main_table.start_date ELSE date END AS date, `transaction`.`entity_id` AS `transaction_id`, `transaction`.`amount`, `transaction`.`date`, `supplier`.`business_name`, SUM(amount_sum) AS `current_amount` FROM `objective_table` AS `main_table` LEFT JOIN `transaction_table` AS `transaction` ON (transaction.supplier_id = main_table.supplier_id) INNER JOIN `supplier_table` AS `supplier` ON (supplier.entity_id = main_table.supplier_id) GROUP BY `main_table`.`entity_id` LIMIT 20

Suggestions? I’m going crazy

firewall – iptables: employ DROP or REJECT rules in non-‘filters’ tables, namely the NAT table?

For an iptables(8) firewall that is otherwise “properly locked down” in the filter table, is there any use to employ DROP, REJECT or similar, terminating, “blocking or disabling” rules in non-filters tables, namely the NAT table (or the mangle, raw, or security tables for that matter)?

We specifically want to close any, potential “holes” in our firewall.

NAT‘s POSTROUTING chain does not accept a DROP or REJECT target on at least one of out team’s systems:

$ iptables -t nat -P POSTROUTING DROP
iptables v1.6.1:
The "nat" table is not intended for filtering, the use of DROP is therefore inhibited.


Try `iptables -h' or 'iptables --help' for more information.
$
$ iptables -t nat -P POSTROUTING REJECT
iptables: Bad policy name. Run `dmesg' for more information.
$
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 18.04.4 LTS
Release:    18.04
Codename:   bionic
$

sqlite – How to query/join tables where foreign key constraint is set in schema

I’m relatively new to sqlite and databases in general.
I’ve successfully setup a schema of 4 tables, each having a foreign key pointing to its parent table. The schema also has the foreign key constraint set on the child keys.

Performing a query where every table is joined with the SELECT * FROM…JOIN…ON ……… works without issue, but it is laborious manually connecting the keys.

My question is: Is it possible to perform a query/join of the tables without the ON statement which links each parent/child key relationship? Since this was already established in the schema definition, this seems to be redundant. I’m trying to simplify the sql command and make use of the parent/child relationship already defined. When I perform said query but omit the ON statements, the results are not what I expected. Any guidance is appreciated.