Thanks for replying to these questions.
Every module contains a /sql/mysql.sql file (appointed in xoops_version.php), which contains the table structure for the module's specific tables. These tables are created at module install and removed with uninstall.
Okay. Is there somewhere .sql file for XOOPS table structure?
No, all tables are MyISAM tables and referential integrity is only possible with the InnoDB storage engine, which is not always available.
Yes, but how the references then work between the tables in the XOOPS database? I'm not too familiar with MyISAM even if I tried to study it a bit with Google.
Such things are not possible between two tables.
If the (unique) primary keys (are used to) point to each other you have a one to one relation.
That's what I thought too.
I think you are misinterpreting the functions between these two tables. AFAIK When a banner has had its required display times it is copied to the other table and deleted in the first table and the 2 bid are both PRIMARY KEY, but are unrelated.
They do have both a 1 to many relation with bannerclient trough their cid field. One client can have many running and finished banners.
Here's a screenshot from DBDesigner 4. I used Reverse-engineer to get the table structure from the XOOPS database. I selected only few tables to be fetched to describe my issue.Screenshot
I have numbers for every "connection" between tables.
1. Relations between "xoops_newsblock" and "xoops_bannerfinish" tables. Key used is "bid" for both ways.
2. Relations between "xoops_newsblock" and "xoops_banner" tables. Key used is "bid" for both ways.
3. Relations between "xoops_banner" and "xoops_bannerfinish" tables. Key used is "bid" for both ways. Below of these tables (marked with "3." also), there are Relation Editors for these two relations. We can see they are 1:N relations and both have "bid" as the "foreign key". (same goes to number 1 and number 2 relations in the picture")
4. Relations between "xoops_bannerclient" to "xoops_banner" and to "xoops_bannerfinish" tables. Key used is "cid" for both like you explained.
Here's documentation for DBDesigner, and a precise link to Relation Editor partOnline documentation of DBDesigner 4, Relation EditorOnline documentation of DBDesigner 4
In the first link, we can see these
1:n one - to - many relation
Explanation: One row in the source table matches many rows in the destination table.
Example: A relation between an [order] table and a [orderproduct] table. One order can have many products and a product in the [orderproduct] table is only part of one order.
1:n (Non Identifying) one - to - many relation, FK not in PK
Explanation: One row in the source table matches many rows in the destination table but the Foreign Key in the destination table is not in the Primary Key Index.
Example: A relation between a [payment] table and an [order] table. Each row in the [order] table as a [payment] assigned to. Only [idorder] is the Primary Key field in the [order] table.
So is this just a flaw in DBDesigner that it misinterpretes these relations? Because according to the software, they are 1:N relations. And to me, it's strange. Isn't it that if that is the case, they are actually "same" tables (and why aren't 1:1 relations used then?) Confusing.
edit. Hmm, now I thought that is it with MyISAM that you have to always do the Cartesian product with the two tables which should have relations between each other? And with WHERE clause pick up the correct rows.
If this is correct, then DBDesigner possibly can't guess which tables really references to each other and it just puts every table referencing to each other which have the same keys??? And the functionality of the database is just in code where the database is really used.
Is it so?