Symphony.

Some people (like me) who are new to Symphony wonder about the many tables in the (default) Symphony database. They wonder what all the sym_entries_data_# tables are about and why Symphony (seemingly!) has so many db queries for each page load.

It is certainly not necessary to understand the Symphony db schema to work with Symphony and the 'many queries' topic has been discussed a little but I wonder if a Symphony veteran/core dev could give us a brief overview of how the various tables relate to eachother, and why…

The only Forum posts I could discover are this one by Alistair, explaining the DB scaling benefits of the current setup and this one by Nick explaining the many queries.

I am no DB expert but I would benefit from having a clearer insight in how the tables relate. My lack of overview makes it difficult sometimes when e.g. I need to manually alter some tables/add some content. I don't quite know where to find certain content etc. (just now I had to manually add some Upload File info to some entries which took some searching).

My current understanding: Entries do not contain content directly but are related to a Section in sym_entries. All current fields (of every Section) are in sym_fields. Every field type has its own table (e.g. sym_fields_input). Content is related to these fields in the sym_entries_data_# tables.

Given the scenario of me wanting to change a, say, simple input text, how would I best find the piece of content to change? The challenge is finding the proper sym_entries_data_# table.

What I do a.t.m. is:

1. Find the proper Field ID (Web inspector in Section editor, yeah really)
2. Look in sym_fields_input for the row with field_id from [1.] and note its id
3. {STRESS} because there is no way to find which sym_entries_data_# table I should look in…

…so I check all sym_entries_data_# tables and think "There should be a better way" ;)

First of all, install Entity Diagram. It exposes Section and Field IDs in the Admin. Priceless.

Sections have their IDs, Extensions have their IDs, Fields have their IDs, Entries have their IDs etc.

Content is related to these fields in the sym_entries_data_# tables.

Indeed, each field holds data for related entries in sym_entries_data_#. If field ID is 5, then you can find your info in sym_entries_data_5.

In sym_entries_data_# tables there is the entry_id column. Guess what that is :)

So, if you have an article and it's entry ID is 27 (www.example.com/symphony/publish/articles/edit/27), and the Upload Field has an ID of 5, the corresponding data can be found in sym_entries_data_5 where entry_id == 27.

Doh! I never realized the sym_entries_data_# number was the corresponding field ID! I simply assumed this was some auto-increment (which makes me an idiot).

Thanks, makes sense. So, to get a particular field (ID 100) value for entry (ID 50) you would do: SELECT * FROM 'sym_entries_data_100' WHERE 'entry_id' = 50;

Thanks (also for the Entity Diagram tip).

Pretty much exactly what vladG said. The Entity Diagram is an invaluable extension if you are building an extension, or planning on playing with the database directly.

Don't feel too bad, it looks like an auto_increment because it almost is. The fields table using an auto_increment to get the Field ID, so it almost appears that way.

A section has a row in sym_sections. Every field in a section has three things:

1. a row in sym_fields which is the generic structure of the field (what section it is in, the type, label and layout position)
2. a row in sym_fields_# where # is the field type (e.g. `tblfieldsinput) which is the specific structure of the field (specific to the field type) e.g. a text input field stores its regex validator string here
3. a table sym_entries_data_# which is a separate table for every field, which stores the data for this field only

To build an entry, you:

1. find the entry in sym_entries to get its entry_id and section_id
2. look up the section fields in sym_fields for this section
3. look up and join the table for each of the found fields

As people have said, you can get field IDs either from entity diagram, the section editor, or the entry creation/edit forms (you can target a field in the DOM using #field-x where x is its numeric ID.

@vladG @brendo @nickdunn thanks guys. It's much clearer to me now.

This will be a helpful thread to point people to who have similar questions ("Please tell me I am not alone…")

Please tell me I am not alone…

Well, you weren't alone. Being at the first contact with anything related to the Web world and OOP PHP (through Symphony, in autumn 2010), when I looked at the tables I said: "What the f..." and ran away back to Admin interface :))

The first time I saw the tables I thought my site had been hacked.

I am so going to start a funniest-quotes-on-Symphony Tumblr…

@ShitMySymphonyDeveloperSays?