In terms of scalability, optimization, and flexibility, it would be much more useful to have CCK create an entirely new table with information for that form instead of lumping it all together into a single CCK table.

First of all, instead of having a row for each field entry, there could be a single row and each column could be a field entry. Secondly, this table could much more easily be used by other modules. It's much more user-friendly when new developers work on an existing database. Lastly, instead of having to query a ton of rows for a single node, you could simply query the table for a specific row id.

Comments

magnestyuk’s picture

magnestyuk commented

Not sure if this relates, but I find it confusing that field data is stored in two ways. If I understand this right, data from reused fields goes into its separate table (=one table per field), while data for fields that are used by a single content type are stored in the table of the content type itself (=one table per content type, with fields as table columns).

MattKelly’s picture

MattKelly commented

Yes, exactly.

My proposal is to have a module that will automatically create a new table with unified data. Fields will be fetched by getting the column names, and the data will simply be stored in rows. It will make it MUCH more efficient and readable.

Flexinode is meant to be static. Sure, other modules could interface with it, but it's database is messy because it stores everything in the same databases (fields and data)- which creates big pains when you try to grab data out with other scripts.

Sid_M’s picture

Sid_M commented

I agree that the current approach is somewhat confusing. However, if I understand it correctly, I don't think the proposed solution will work. I think it runs into problems when a field can have multiple values. Let's say the first node created using this field creates 2 items for the field. Now you need 2 columns, 1 for each item. If the next node creates 4 items for the field, it will require 4 columns in the same table. This means that the first node now has 2 null columns. It also means the number of columns in the table may have to change every time a new node is created. And what if we delete the node with 4 items, do we now also remove the 2 columns from the table?

The real problem is that querying the table becomes a bear. To look for a value in the variable-item field, we have to determine how many columns it currently uses, and then search all of those columns: e.g. SELECT * FROM node_type_table WHERE multi-item1 = "searchTarget" OR multi-item2 = "searchTarget", etc. This gets nightmarish fast.

It is exactly because of such complications that it is standard database practice to split off a field that can have a variable number of items into a new table with a row/record for each item. This is how cck works.

We could get around this by serializing all the data for a field into a string, and putting that into one column, thus making the field always require only 1 column. However, this undermines the ability to have the database search that field.

Sid_M’s picture

Sid_M commented

Just to clarify a bit. Querying for the content of a node would be easy since one could always do SELECT * FROM node_type_table WHERE vid = "vidForTheNode", however the problem I outlined arises when one wants to search for nodes that have some characteristics, and that characteristic is stored in a multi-item field.

jonbob’s picture

jonbob commented
Status: Active » Closed (works as designed)

As Sid_M states, this proposal can't work because of multivalued fields.