-1
I’m working on a little personal project using Libreoffice Base. The idea is to make a small database to catalog my comic book collection. I am a complete beginner (both in Database and in Libreoffice Base) and I came across a stalemate.
My problem arose when I was trying to find a way to record when a character (e. g. Spider-Man) enters a group (e. g. Avengers). As it is a many-to-many relationship (after all a character can belong to many groups and a group can have several characters) I had to create an associative entity. I decided to call the table character_group.
The problem is that the database should record not only whether a character belongs to a group or not, but also from which comic he enters the group and (if applicable) the comic book in which he leaves. It is also desirable that it is possible to record multiple occurrences of input and output (after all a character can enter, exit and then in the future enter the group again, and so on). After much thought, I finished with the following structure for character_group:
| character_ID | group_ID | start_issue_ID | end_issue_ID |
Where:
character_ID = character code (linked to the primary key of the character table)
group_ID = group code (linked to the group table primary key)
start_issue_ID = code of the comic book in which the character entered the group (linked to the primary key of the comic book table)
end_issue_ID = code of the comic book in which the character left the group (linked to the primary key of the comic book table)
Initially I thought of putting the four fields as the primary key, but a character may be in a group and not be out yet, so the end_issue_ID field cannot be empty. In this case, the primary key would be only the first three fields (i.e. the character code, group code and comic book code in which he joined the group).
Apparently my problem was solved, until I went to try to establish relationships between tables. It turns out that both start_issue_ID and end_issue_ID are related to issue_ID (the item code in the item table). However, the Libreoffice base does not allow (as far as I know and tried) to establish a relationship between two fields in the same primary key.
I’m pretty sure that modeling is wrong, and I would be very grateful if someone could tell me where the problem is.
Observing: I was asked for the diagram or DDL of the tables. I suppose that’s it. I don’t know how to provide the DDL of the tables.
from what is written I suppose that a character can "get in and out" of a group more than 1x, so I would need a field like "start_date" in the composite key to ensure that there would be no problems... the question is "do you really want to control this in the model"? can start from the idea that your table solves
n:mjust show you which group a character participates in at that time, create an automatic primary key, and use character and group as foreign keys only, it would simplify the logic and make the model simpler– Ricardo Pontual
I added the diagram in the post. If you can explain to me how to give you the DDL I can do without problems.
– user254922
Ricardo Pontual, thank you for your contribution! From what I understand, if I did in this way that you suggest I would only have available information that the character belongs to a group in the present, correct?
– user254922
not the same template you proposed, the only difference is to create a simple primary key to avoid "Duplicate key" errors, thus
character_group (id_character_group pk, character_ID fk, group_ID fk, start_issue_ID fk, end_issue_ID fk)– Ricardo Pontual