Data Link in SQL

Question

Data Link in SQL

Navigation

#1 by (2 votes)

5

I really do not handle almost anything database and related, but I'm doing an "admin" system here for a client and wondered about the following point:

I have two tables, one that saves the images themselves, with the link and everything else that saves the event, it has a field called images , which I would fill with a JSON with ID's of the images table, I would like to know if this is "cornice" as a friend of mine says, if that is the best solution ?, ahh I'll leave here the SQL of the tables for easier visualization. p>

-- 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;

sql

asked by anonymous 07.07.2015 / 19:59

1 answer

Send message Whatsapp by link on site Table does not appear in return Json

score 2 · Answer 1

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

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

It's not intuitive to work with JSON in pure SQL language - maybe one or the other database system will help you out, but I think this would be easier to find in non-relational databases (ie, well different from the trio SQL Server, Oracle and MySQL);
But mostly, there is a very strong relationship between photos and events. Relational databases have this name because they have well-structured ways to deal with those relationships. When doing the relationship through text in columns, you do what is technically called a "weak relationship", which is when you take responsibility for ensuring relationships in place of the bank. This is a serious form of reinvention of the wheel , and the more your system becomes complex, the more you will suffer from rework and depression.

The best way to deal with this is to have 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 must be daughter from the event table. The table of photos needs a column, of the same type as the ID column of the event table. This particular column will be a foreign key , which will point to the primary key in the event table. You can call this column from id_evento or some other name that is self-descriptive.

The effect of this is that, by default:

You can not delete events that have associated photos;
Every photo should already be entered in the database pointing to an event.

This is one 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 exclude all photos.

From now on it's all setup. You only have to know what the best configuration is by "putting your hand in the dough" and tinkering with the system, so there's no point asking the "best" way to set up relationships here. What I recommend is that I play around with this, because learning from experience is far superior to any other form of learning.

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

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