Drupal 7 Fields Doubts

I’m not sure if anyone here has looked into Drupal 7 fields but as cool as it seems it looks like it can quickly get out of control and bring a site to a halt or am I wrong? I mean… creating a new table for each meta data field for a couple of fields isn’t horrible but. However, lets say you need something like 100 or even 200 fields of meta data. In that case there would be at least 400 separate tables (including revision tables). That just doesn’t seem right. I haven’t dived into the specifics of how fields are loaded (attached?) but I can only assume its done using a single query per field, large union , or joins at least equal to the number of fields per entity and bundle combination. Each one of those cases has significant drawbacks when talking a large number of fields. It seems like the only way to circumvent the stress placed on the database would be to cache full entities after they are built. I would appreciate it is someone more educated on the topic could offer some insight as I’m in middle in of building something and I have by doubts with fields considering the the large number of them that I will be using amounting to over 200+ field value tables most likely. It seems like there are quit a few intelligent minds behind Drupal core so maybe I shouldn’t worry? – I’m sure there is a good reason for the way fields have been implemented in Drupal 7, perhaps? Having the background I do though the whole thing kinda worries me…

Yeah… Looked at that. I was expecting the same behavior as what was was built into D6: Create a Content Type (CT) and all the new fields would go into the same table as the CT. Once a field got reused in a new CT, the field would be broken out into a separate table… joined on by whatever new CT used the field. That would work GREAT for 200 fields in the same table (sorta) but yes, I imagine this behavior is shooting ones self in the foot if you have to ask Drupal to Join all those tables to get your content out…

That being said, could you summarize your case of why you need 200 fields in one content type? I would imagine managing such data would be a challenge for any CMS – and doubts like that make me wonder why you would use a CMS at all instead doing some custom coding. I’d bet the big drop devs have considered this use case and decided at some level it’s better to make the fields reusable as a separate entity right from the start.

Thoughts?

Well I was attempting toe create a Real Estate module for Drupal 7. My first thought was to use fields, but now I’m pretty certain that is a bad decision. Instead I think the best way to go about it is actually using my own entity that way I can properly sculpt the domain yet plug into many powerful features of the core and available modules such as views. the other thing I was thinking is to create my own data storage module so that I can store many fields in a single table – similar to the way the image module handles its data using fields. I haven’t had time recently to dig deep into it but I believe that node reads are cached serialized objects. That is how I would assume the system is able to use the field data storage method it does yet maintain some type of optimization.

I’ve always been curious about the drawbacks of these massive amounts of tables, you also have to assume if they kept it all in one table, they would probably have to use a longtext column type to support long text, short text, numbers, etc. So imagine a longtext column holding “2” in it.

I know this is how Pods is currently doing, or planning on doing their system for WP.

However, lets say you need something like 100 or even 200 fields of meta data. In that case there would be at least 400 separate tables (including revision tables). That just doesn’t seem right.

I have only experimented with Fields but used Drupal on several sites now (v6 and v5). I am pretty sure CCK actually changes the schema of the table, so adding or removing fields changes the table schema not introduces more tables per field. I think at one time this is how it was done and eventually changed to schema changes.

Unless fields work totally different than I understand, this would seem to me to be a major critical design flaw.

That being said, if your collecting 200 fields of information in a single table, your design is way off as well, so really?!?

Cheers,
Alex

PCSpectra, Fields are stored completely different in Drupal 7 – single field per a table using default data storage.

Drupal 7 and CCK use a data type that reflects the type of data being stored. All columns are not longtext.

There is a significant amount of meta data that can be associated with a real estate listing. Using the core features and plugging into Drupal seems like it requires the use of fields to store this data in separate tables or creating an entity with all columns. Perhaps there is another way, but it will probably require some heavy lifting and may even loose the ability to take advantage of things like views. I have just started diving into D7 so we’ll see.

We’re currently at some 250 different attributes per property and a bunch of free text fields (in 12 languages).

We ended up storing them in a bunch of tables like so:

object: general table identifying the property
object_attr_boolean: table storing values associated with boolean fields
object_attr_integer: table storing integer values associated with property attributes
object_attr_name: table storing attribute definitions (e.g. the 250 different attributes, what the choices are etc)
object_attr_text: table with TEXT fields
object_attr_varchar: table with VARCHAR fields
(and about another 10 tables)

The fields in those tables are ‘bound’ together using a bunch of views.

The tables are not exposed to the website, only to the management application (e.g. where customer can manage their listings).

The website itself uses a couple of Solr cores for facetted searching/ filtering and actually serving the content of the properties.

We’re at +/- 300K properties (of which +/- 100K are online). I’m never serving that number of properties with that number of attributes and with facetting from MySQL ever again, it’s not the right tool for that job.

As you’re building a module for Drupal I don’t know if you have a real-world need for such an application, but if you do, and you’re expecting a large number of properties in your module, I’d strongly suggest you investigate using something like Solr as indexing engine. :slight_smile: