Redis vs MySQL for simple database

Question

I want to create a simple database that have to store nodes, their functionalities and address to notify in case of problems. Initially I was thinking on using a simple SQL database:

CREATE TABLE IF NOT EXISTS push_notifications (
  node text NOT NULL,
  functionality text NOT NULL,
  address text NOT NULL,
);

where nodes and functionalities can have many address, N to N. This way to get the address I will just execute this two sentences:

SELECT address from push_notifications where node=XX and functionality=YY;
SELECT node, functionality from push_notifications where address=XX ORDER BY node, functionality;

However after reading a bit, I have several doubts on:

• Is that ok for a database that initially won't have more than 10000 entries?
• Should I use the normalize way of organizing tables, that is, one for nodes, another for functionalities and another for addresses and the use JOIN in SELECT? Then, how could I automatically delete the entries from the tables that are not longer linked, ie a node that has no functionality and no endpoint?
• Should I use for instance a simple database engine like Redis, setting keys to node-functionality (string and value a list of addresses) and another set of keys to the endpoints (hash)?

I would like to add that I will be using Java to handle the access to data.

Thanks for you help. I would really appreciate and advise on what is the best way to do things like that.

Edit: option for select with multiple simple tables (I think is OK)

 CREATE TABLE IF NOT EXISTS node (
   id integer PRIMARY KEY AUTOINCREMENT,
   iri text NOT NULL,
   UNIQUE(iri) ON CONFLICT IGNORE -- ON CONFLICT REPLACE
 );

 CREATE TABLE IF NOT EXISTS functionality (
   id integer PRIMARY KEY AUTOINCREMENT,
   name text NOT NULL,
   UNIQUE(name) ON CONFLICT IGNORE -- ON CONFLICT REPLACE
 );

 CREATE TABLE IF NOT EXISTS address (
   id integer PRIMARY KEY AUTOINCREMENT,
   url text NOT NULL,
   UNIQUE(url) ON CONFLICT IGNORE -- ON CONFLICT REPLACE
 );

 CREATE TABLE IF NOT EXISTS push_info (
   node integer NOT NULL,
   functionality integer NOT NULL,
   address integer NOT NULL,
   UNIQUE(sensor, endpoint) ON CONFLICT IGNORE, -- ON CONFLICT REPLACE
   FOREIGN KEY(node) REFERENCES node(id) ON DELETE CASCADE
   FOREIGN KEY(functionality) REFERENCES functionality(id) ON DELETE CASCADE
   FOREIGN KEY(address) REFERENCES address(id) ON DELETE CASCADE
 );


 SELECT address.url as address
   FROM address
 INNER JOIN push_info
     ON address.id = push_info.address
 INNER JOIN node
     ON node.id = push_info.node
 INNER JOIN functionality
     ON functionality.id = push_info.functionality
 WHERE
   node.iri = "node1" AND
   functionality.name = "functionality1";

Answer 1

Normalization You have a table. Before you start "normalizing", or start talking about "normalizing", find out what normalization is. Here is the key question: Can you replace this table by smaller ones so that they always join to it? For that to be so, it must possible to phrase the criterion for a row to be in the table in a given application situation as "... AND ..." for one or more ANDs. If you can, normalization may suggest doing that replacement. If you can't, it won't. (Cardinalities do not in general allow us to determine that.)

Further tables Only you can know whether you want to store information that this table cannot tell you, so that you need more tables. Eg if you want to record that a particular node exists even though it does not participate in the application relationship that this table represents, then you need another table.

Relational vs not Relational databases allow generic declarative querying and integrity enforcement, with certain implementation costs (and some automated optimization). Other data structures each support specialized & largely non-declarative querying and integrity enforcement, with improved implementation costs, but with other cases typically awkward and/or expensive. You have to know your usage patterns and how costs and benefits trade off among these many dimensions in order to decide which design is "best". We can always reasonably start with a relational design then specialize where shown necessary and desireable.