AppGini Community Forums

Hello, i absolutely need to create a many to many relationship.
My database is used as a professional contents directory (video, books, audio, articles, authors, publishers, comments => as tables).
The fact is : i want to link contents among each other. For instance, inside a video or an audio content, one or many book(s), or other video, audio articles (...) can be quoted. So there is here a many to many relationship : many contents of any type are linked to many other contents.
The idea is :
When a user registers a new content (e.g : a video) he just should have to add child contents (here, for instance, the books, articles that the video talks about). The issue is : I tried everything but i couldn't manage to raise that issue (problem of circular reference when i add a lookup in each table to all other tables).
I for instance tried to create a lookup that looks in many table (with the button advanced), but it doesn't work...could Ahmar help me on that point ?
And, in addition to that, there must be absolutely no duplicates on the fields Title and URL (for audio /video / article contents)


I've got an idea to solve that :

Creating a table "relations_between_contents" with many lookups :

id_relation
type_of_first_content => this field should be a radio option in order to let the user choose what lookup to use (what table to search in) for content#1
video_tolink => lookup
audio_tolink => lookup
article_tolink => lookup
book_tolink => lookup
...
type_of_second_content => this field should be a radio option in order to let the user choose what lookup to use (what table to search in) for content#2
video_tobelinked => lookup
audio_tobelinked => lookup
article_tobelinked => lookup
book_tobelinked => lookup
...

=> But that way requires to hide some lookup fields (for ID# xxx, the user could only link a content to another : e.g : video_link <=> book_tobelinked)
It's absolutely not practical and not very simple for the users, they have to register a new content, its children, and then to separately indicate links between contents through another table...

Any ideas ?

Thanks in advance.

Hi,

I suggest that you try to reorganize your data model.

Instead of separate tables for videos, audios, articles and books, create one table called, for example, media. Besides title, media table should have attribute called media_type (video, audio, etc). Many-to-many relation on media table is recursive, i.e. media table is in relation with itself. Since this is many-to-many, such recursive relations are implemented as a separate table with parent_media_id and child_media_id attributes (there can be other attributes as well). This is the table that you call relations_between_contents, but, instead of many lookups, it has only two (parent and child IDs).

Attributes, that are specific for different medias, can then be stored in another table which is in master-detail relation with media table. Let's call this table media_details. It should have the following attributes:

• id (as a primary key)
• media_id (to connect it with its master media record)
• attribute_type (e.g. URL, video, sound, text, image, etc.)
• attribute_content (blob field to contain particular media)
• etc. (anything else that you need)

Handling recursive relations is tricky in any development system. So, do expect to do lots of customization after you generate initial code with AppGini.

Good luck!