So that when we remove one the other gets removed or when we update one the other gets updates, or, when we try to remove the other doesn’t let us doing it…
Because associated with the images storage we will have other informations like, filename, mime_type, file_size, file_data … and those informations don’t belong to Associations hence, they seem to be more related with Images or files, exclusively.
Ok… on the last hours (yes hours! ) I’ve come up with something like this:
CREATE TABLE association
(
id_association INT not null PRIMARY KEY AUTO_INCREMENT,
name_association varchar(255) not null,
address_association text,
post_cod varchar(50),
telephone_association varchar(100),
email_association varchar(100),
url_association varchar(255),
index (name_association)
)
CHARACTER SET utf8 COLLATE=utf8_general_ci,
TYPE=InnoDB;
create table image
(
id_image serial PRIMARY KEY,
name_image varchar(255) not null,
mimeType_image varchar(255) not null,
fileSize_image int not null,
fileData_image longblob not null,
id_association INT,
FOREIGN KEY(id_association) REFERENCES association (id_association) on delete cascade,
index (name_image)
)
CHARACTER SET utf8 COLLATE=utf8_general_ci,
TYPE=InnoDB;
Is this ok?
If so, please advice:
When we delete an image, the correspondent association should NOT be deleted. By taking no options on that, we are assuming this procedure?
in this relationship, the refencing table is the logos, and the referenced table is the associations
deleting a referencing row is always possible, and never has any effect on the referenced row
you can delete a logo if you wish, but the association will stay
the ON DELETE and ON UPDATE options, which are specified in the referencing table, refer to delete/update actions taken on the referenced table
so if you delete an association, or change an association’s id number (although with an auto_increment, you would never need this), then you have options as to what to do to any related logos
I’m sorry to repost here, but it is so related that I found useless to create a new post:
Regarding this 1:1 relation here, I’m facing a hard time trying to understand how will the insert work here.
At the same time we insert something on Associations a corresponded Image should be inserted into the Image table.
On the logical part, I can have two inserts one after the other.
The first puts the data into Associations and the second, inserts an Image.
But… and those are my questions:
What if one of the tables, for some reason, doesn’t work - we could have something on the Associations table, and nothing on Image table, supposing the image one was the table that didn’t work for some reason.
What if two users, at different points, for a bizarre coincidence, are trying to insert data at the same time, then it could be the case that we have Two associations but with switched logos. :s
What should we do on those cases?
If those cases are very Very rare (I’m talking about a very little database here), then using one insert after another is the way to go?
Update: Why do I need those inserts to happen almost at the same time?
I’m assuming that, for retrieving the association and the correspond logo, I would join both tables where the id of one is equal to the fk of another, hence, when we insert (or delete) the id’s should be synchronized.
I’m not getting this right perhaps… :s
No.
You’re using an autoincrement key field in your associations table. Assuming that you’re using PHP, you’ll use mysql_insert_id() to retrieve the ID generated for the AUTO_INCREMENT column by the previous query. It will give the id generated by the previous query in that session, not the last one generated by another session.
I guess other languages will have a similar function.
) I’ve come up with something like this: