I want to know which retation there are between tables in a database (phpMyAdmin) to creat a Entity Relationship Model, but I can't see any relation anywhere else in my database.

I use TYPO3 ver. 10.4.21 with the extension: pizpalue and sitepackage builder.

I created a custom content element in my sitepackage builder extension and I want to know which tables of my database have some relation. My all variables for my content element are in the table "tt_content".

If I go to my phpMyAdmin and open the "Designer", then I can see many tables, but no relation:

It looks like every table has just a primary key, but not a secondary key (foreign key).

I have some questions for my problem.

1. Is there a relation between the tables at all? In other words, are all tables independent?
2. Which tables should I use to create a diagramm like entity relationship model or databbase model?

A example for a entity relationship model is (Source: Wikipedia):

My first idea is that I need following tables to create a diagramm: pages, tt_content, maybe also be_users. Do I need other tables for my diagramm?

If there is no relation between the tables, I can't add a entity (relation) in a entity relationship model, right? Or is there anything a good way to create a diagramm?

I'm sorry for my stupid question. But I don't have any idea, how TYPO3 and tables of the database work together.

I hope someone can give me some answer or ideas. Thank you.

CodePudding user response：

As you have found TYPO3 consists of a lot of tables. These tables have different tasks.
If you want to show each relation your diagram will become more complex than to give an impression of the data. [1]

As TYPO3 is a CMS the most important tables are the tables which structure and contain the content of the frontend.
This tabes are pages and tt_content, but dependend on your installed extensions further tables are needed.

Of course there are other tables important for the frontent as sys_file and sys_file_references which manage the used files like images (FAL = File abstraction Layer).

Dependend on your used functionality other tables are used or ignored:
if you have a frontend login you probably need the tables fe_users and fe_groups which can control visibilty of content.

In general you can get a more detailed picture of table relations in the TCA (Table Configuration Array) of TYPO3. Here you can find the declaration of all relevant fields as they are used in TYPO3 (independent from the definition in the database)

More information about TYPO3 can be found at https://docs.typo3.org

maybe you can get more specific information here:

• API:Database Structure
• TCA

[1] nearly each table has some common fields which build relations to the tables pages, be_users, fe_groups which are used to structure the data in the back_and and control visibility in the front_end.
Each record has the field

• uid (unique ID) to identify the record
• pid (page-ID, sometimes also called parent-ID): relation to pages: where is the record stored (all data is stored in a tree of 'pages' build from the records in the table pages. so as pages-records are stored inside pages-records a tree is build (like folders on a hard disk)
• crdate (creation timestamp)
• tstamp (timestamp of last change)
• cruser_id (creating user ID) relation to be_users
• deleted (deleted records are not visible in backend)
• hidden (visible in backend, but hidden in frontend)
• starttime (timestamp when frontendvisibility starts)
• endtime (timestamp when frontend visibility ends)
• fe_groups controls frontend visibilty depending on logged in frontend user.
• sorting order of records

Language handling:

• sys_language_uid language of record (relation to sys_language)
• I18n_parent (Internationalization parent) of translated record (relation to same table)
• l10n_source (localization_source) source of translation (relation to same table)

Versioning:

• t3ver_oid (TYPO3 versioning original ID) (relation to same table)
• t3ver_... versioning information

Category:

• category (relation to sys_category)

this last field is a good example of an indirect relation as it uses the intermediate table sys_category_record_mm for the real relation. There you have fields for the uid of the sys_category record (uid_local) and fields for uid and name of table and field (uid_foreign, tablenames, fieldname) of referencing tables/fields.
In the field category only the count of references is stored.