Data link in SQL

Question

I don’t really know almost anything about databases and the like, but I’m doing an "admin" system here for a client and I wondered about the following point:

I have two tables, one that saves the images themselves, with the link and everything and everything that saves the event, in it has a field called images, which I would fill with a JSON with the table ID’s images, I wonder if that is "cornice" as a friend of mine says, if what is the best solution? , Ahh I’ll leave here the SQL of the tables to make it easier to view.

-- Table eventos
CREATE TABLE IF NOT EXISTS eventos (
  id int unsigned NOT NULL auto_increment ,
  slug VARCHAR(255) NOT NULL ,
  tittle text NOT NULL ,
  content text NOT NULL ,
  data date NOT NULL ,
  images text NOT NULL ,
  PRIMARY KEY (id, slug)
) CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=MyISAM;

-- Table images
CREATE TABLE IF NOT EXISTS images (
  id int unsigned NOT NULL auto_increment ,
  title VARCHAR(255) ,
  slug VARCHAR(255) NOT NULL ,
  PRIMARY KEY (id, slug)
) CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=MyISAM;

Answer 1

The term "cornice" that your friend probably used refers to the use of bad practices, because maintaining bad code is the kind of thing that in Brazil we call "horn work" or "punishment to the cuckold".

Using JSON as you described can be seen as bad practice for the following reasons:

• It’s not intuitive to work with JSON in the pure SQL language - maybe one or another database system will help you with this, but I think it would be easier to find in non-relational databases (that is, quite different from the trio SQL Server, Oracle and Mysql);

• But mostly even - there is a very strong relationship between photos and events. Relational databases have this name because they have well-structured ways of dealing with these relationships. By doing the relationship through text in columns, you do what you technically call "weak relationship", which is when you take responsibility for ensuring relationships in the bank’s place. That’s a serious way to reinvention of the wheel, and the more your system becomes complex, the more you will suffer from rework and depression.

The most appropriate way to deal with this is to make a relationship with foreign keys. In this case, since an event can have multiple photos, but a photo is only part of an event (right?), the photo table should be daughter event table. The photo table needs a column of the same type as the event table ID column. This specific column will be a foreign key, that will point to the primary key in the event table. You can call this column from id_evento or some other descriptive name.

The effect of this is that by default:

• you will not be able to delete events that have photos associated with them;
• every photo should already be inserted in the database pointing to an event.

This is a way to ensure data integrity. There will be no photo without event (although there may be event without photo). The rules of this relationship can be changed... For example, deleting an event can automatically delete all photos.

From now on, it’s all setup. You can only know what is the best configuration when you "put your hand in the dough" and fiddle with the system, so it’s no use asking the "best" way to set up relationships here. What I recommend is that you play a lot with this, because learning by experience is much superior to any other form of learning.

And to view all photos of an event, you can use INNER JOIN:

SELECT
    i.*
FROM
    images i INNER JOIN eventos e
WHERE
    i.id_evento = e.id