Best way to store a list of integers/strings into mysql?

Question

I am writing a Golang application in which taking input from a user for an IP/hostname results into the below output. Now as a requirement I want to store all ports for a host in a MySQL Db and return it from the tables along with the history whenever it's requested. What's the best way to structure this data in DB for ease of retrieval? Considering efficient design practices

   Starting Nmap 7.91 ( https://nmap.org ) at 2021-01-21 18:50 Eastern Standard Time
    Nmap scan report for localhost (127.0.0.1)
    Host is up (0.00090s latency).
    Other addresses for localhost (not scanned): ::1
    Not shown: 997 closed ports
    PORT    STATE    SERVICE
    135/tcp open     msrpc
    137/tcp filtered netbios-ns
    445/tcp open     microsoft-ds

"Considering efficient design practices" --- "efficiency" is not the absolute meathure. There are million contradictory solutions each of which is "efficient" for given constraints. — zerkms, Jan 22 '21 at 00:27

score 1 · Answer 1 · edited Jan 22 '21 at 08:58

1

I would create one table for the NMAP report:

CREATE TABLE nmap_reports (
 nmap_report_id BIGINT AUTO_INCREMENT PRIMARY KEY,
 nmap_version VARCHAR(10),
 reported_at DATETIME COMMENT 'UTC',
 host_status ENUM('up','down') NOT NULL,
 host_latency_seconds NUMERIC(9,5),
 host_address VARCHAR(10),
 not_shown VARCHAR(255)
);

Then another table for the multiple ports:

CREATE TABLE nmap_report_ports (
  nmap_report_id BIGINT NOT NULL,
  port SMALLINT NOT NULL,
  protocol ENUM('tcp','udp') NOT NULL,
  state ENUM('open','filtered') NOT NULL DEFAULT 'open',
  service VARCHAR(20),
  PRIMARY KEY (nmap_report_id, port, protocol),
  FOREIGN KEY (nmap_report_id) REFERENCES nmap_reports(nmap_report_id)
);

So a report would result in one row in nmap_reports, and zero to many rows in nmap_report_ports, each row referencing the row in the first table.

This is the best way to store complex multi-valued attributes to support:

Data integrity
Storage efficiency
Maximum flexibility of queries against the data

It's the correct way to represent a one-to-many relationship.

Avoid storing data in comma-separated lists.

edited Jan 22 '21 at 08:58

Strawberry

33,750
13
40
57

answered Jan 22 '21 at 00:29

Bill Karwin

538,548
86
673
828

CREATE TABLE nmap_report_ports ( nmap_report_id BIGINT NOT NULL Did you mean nmap_port_report _id for this second table? – Neo Jan 23 '21 at 21:05
No, it's a foreign key to the first table. The first table has the auto-increment to generate new values. The second table just references them. I use a convention that where possible, I name the foreign key the same as the primary key of the table it references. – Bill Karwin Jan 23 '21 at 23:19
Field 'nmap_report_id' doesn't have a default value - getting this error for inserting in the second table. From the I am not providing any value to this field for second table, Since I want it to reference from table 1 and have same value. Should I be providing the vaue for this? – Neo Jan 23 '21 at 23:23
1

Yes, you must specify a value since the column is NOT NULL and has no DEFAULT and is not AUTO_INCREMENT. You use the primary key value of the row you want to reference in the `nmap_reports` table. If you just inserted that row, you should be able to use `LAST_INSERT_ID()` to get the value most recently generated. – Bill Karwin Jan 23 '21 at 23:28
thank you that worked like a charm – Neo Jan 23 '21 at 23:41
One of the requirements I have for this project is·that the DB tables are properly indexed. How can I make sure of this? – Neo Jan 24 '21 at 00:07
That's a big topic and not one I can cover in a comment thread. I made a presentation [How to Design Indexes, Really](https://www.slideshare.net/billkarwin/how-to-design-indexes-really), and here's a video of my presenting it: https://www.youtube.com/watch?v=ELR7-RdU9XU – Bill Karwin Jan 24 '21 at 01:01

Best way to store a list of integers/strings into mysql?

1 Answers1