database structure

[kixe]

When I get in contact with processwire after a recommendation of a friend the first step after installing was a look in the database. Why?

I had bad experiences with a shop-system I used, which ended up in digging and modification of the source code to make it work properly. Finally the biggest problem was the database with its questionable structure and data redundancy. I said goodbye to the shop-system after.

With processwire I felt immediately in love because of its super slim and clear structured database without redundance of course from which results a lot of opportunities to grow.
 
But since I use multilanguage support in processwire it makes me a little gripes when I see the way of Language Implementation in the database.

About the conventional structure as an example:
users are stored in pages and passwords are stored fields 'field_pass' which refers to the page (perfect)

Structural changes since multilanguage support:
pages and fields either have data (information about language) stored in column titles like 'name1007' or 'data1007' (not really perfect)

What happened while making processwire multilingual?
The tablefield in the database 'name' in table 'pages' wasn't unique anymore. 'name' from now on would have to be a field 'field_name' like 'field_titel' with its own databasetable. Same to status. The status of a page should be stored in 'field_status'.

Possible solution:
There exists already a field 'field_language' which stores the language of the page 'user'. This field could store easily the language of every page.
Every page in every language should have its own database entry and unique id, even the rootpage with parent_id '0' should have this. All name and status columns should be outsourced to fields. Language relevant columns like 'data1007' in fields are obsolete.
As a result every field becomes multilingual automatically, because it references to a unique site.

I know that many people Ryan first of all have spent a lot of time and work to make this great system available. And I have a lot of respect before this people. I am happy to use processwire and I love it. But since I run in problems when I decided to switch an alternative language to the default language burrowing around in the database I started to think about some facts. I am pretty sure processwire will run in problems following the pursued way.

On the way making processwire to the worlds best CMS the database as the heart of the system should be young, healthy and proper.
 

[ryan]

While I agree that the ID appended fields may not be perfect, they turned out to be the best option after considering many others within the context of the entire system. The direction was chosen very carefully, and I have worked on multi-language databases setup exactly as you described for mass data conversions. There are a lot of considerations that go into the structure that was decided upon from selector engine findability to compatibility, ease of conversion from/to multi-language, eliminating potential overhead for non-multilanguage environments, support for different types of multi-language environments, overall efficiency/speed and much more. The longer term plan is to outsource the ID to a separate table and use predictable names for the current ID appended versions, but that's just a minor change as a matter of portability and not going to matter to most. I also want to mention that we already support any field being multi-language via Language Alternate fields, which may sometimes suit your purpose better than multi-language fields, even for regular text fields. 

The tablefield in the database 'name' in table 'pages' wasn't unique anymore. 'name' from now on would have to be a field 'field_name' like 'field_titel' with its own databasetable. Same to status. The status of a page should be stored in 'field_status'.

Nothing about using the LanguageSupportPageNames module (which adds the new name columns as needed) interferes with name being required to be unique within the parent. That aspect remains regardless of whether using multi-language page names or not. However, it is possible for more than two languages to have the same name for the same page, and this is intended. Some languages use the same terminology and words, so it would actually be a limitation if we enforced uniqueness across them. It also enables use to fall-back to the default language for instances where someone wants to use multi-language page names in some instances and not others.  In a multi language environment, the language is determined from the overall path, not the individual page name. This is an important aspect of multi-language page names, as there is often a lot of crossover in terminology between different languages. This is one reason why we recommend (though don't require) that you at least set a starting segment for the language on your homepage, i.e. /de/, /en/, /es/, etc.