StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to store metadata about the table?
text
Body
copied!<p>I have a table. A large table with 25 columns each containing atomic data about specific entity. Entities are, to be specific, real estate properties (like rooms and houses) for sale, and so the table is called <strong>property</strong>.</p> <p>Every property has a subclass (actually it's called "type", but we'll call it "subclass" to avoid confusion with data types), which at this moment is "already built and for sale" or "under construction, but can be invested in". It also has a lot of attributes, like address, price, etc., most of which are shared among subclasses, but some are not. Attributes have different data types, which are:</p> <ul> <li>Integer numbers</li> <li>Floating point numbers</li> <li>Short lines of text</li> <li>Long chunks of text</li> <li>Foreign keys to other tables</li> </ul> <p>These "other tables" are for selecting from a moderator-editable list of options (like list of the city districts, list of building companies etc.).</p> <p>Moderator should be able to create new properties and edit them. User should be able to view certain property's detailed information and search for properties satisfying a certain criteria, then viewing them as a table, sortable by one of the columns.</p> <p>Depending on the subclass of property, only a subset of property attributes is available to a user for viewing and to a moderator for editing. Also, depending on data type, different HTML code is needed to show these attributes to the user and to provide the moderator with editing controls, as well as different data validation checks should be performed after editing.</p> <p>The list of fields is not dynamic - it is unlikely for the list of columns and how they are displayed to change often, and it is not needed for the moderator to be able to change it.</p> <p>However, since 25 is a rather large number, I'd like to organize and keep in one place all the metadata about the <strong>property</strong> table: the information about what subclasses are the columns for and how should the data be displayed, edited and validated. It would be nice to be able to access all this metadata from my code in some easy way (like an array). I see three options to do this:</p> <hr> <h2>1. Constant PHP array</h2> <p>Just create a PHP file or function that will construct the array with metadata, then include/call it when needed.</p> <p><strong>Pros:</strong></p> <ul> <li>Simple.</li> <li>Fast.</li> </ul> <p><strong>Cons:</strong></p> <ul> <li>Harder to maintain, because of overly verbose and ugly PHP code.</li> </ul> <hr> <h2>2. MySQL database</h2> <p>Create a table <strong>property_meta</strong> in the database and store the metadata there. The new table will contain column name from the <strong>property</strong> table, relevance of the data in this column to each of the <strong>property</strong> subclasses, expected data type etc. Then create a function that will query the necessary fields and return the resulting data as an array.</p> <p><strong>Pros:</strong></p> <ul> <li>Easier to change the metadata.</li> <li>Less code to maintain.</li> <li>It <em>can</em> be later expanded to allow user to change the list of columns. It won't be much of a problem to add or remove columns from the <strong>property</strong> table. Although in my opinion user being able to change database schema on the fly is a serious code smell. Correct me if I'm wrong.</li> </ul> <p><strong>Cons:</strong></p> <ul> <li>Whenever metadata is changed, server database must be updated accordingly. But it will only happen when database schema changes anyway, so no one cares.</li> <li>Slower - to the cost of creating array this will add the cost of talking with the server and the cost of selecting the data from the database. Although the latter is likely negated by MySQL query caching mechanism.</li> </ul> <hr> <h2>3. Separate properties and their attributes into different tables</h2> <p>Create the metadata table as in the above solution, only name it <strong>property_attribute</strong>. Also create <strong>property_data</strong> table with foreign keys to <strong>property</strong> and <strong>property_attribute</strong>, and one more column for attribute value. The <strong>property</strong> table would then only contain primary key and subclass and actual attribute values can only be retrieved with a query with two joins.</p> <p><strong>Pros:</strong></p> <ul> <li>Most flexible solution. If the list of attributes is changed, database schema will stay the same.</li> </ul> <p><strong>Cons:</strong></p> <ul> <li>Each <strong>property_data</strong> row will contain a single piece of data of unknown type. Either store all of them as TEXT or BLOB, or create separate columns for separate data types. Both solutions look ugly.</li> <li>It is unclear how to handle former foreign keys from the <strong>property</strong> table. Automated data integrity checks that are done on every insert become nigh impossible (maybe possible with triggers? I'm not sure).</li> <li>Selecting data will become much harder. The data will be fetched as <code>property_id</code> - <code>property_attribute_id</code> - <code>value</code> trinities, which is not intuitive and requires more effort to properly output.</li> <li>More than that, filtering and sorting by one or more of the attributes will send me to the world of hurt.</li> <li>Very, very slow.</li> <li>It feels like using a helicopter to cross the street.</li> </ul> <hr> <p>Frankly, I don't like either of these solutions. But the second one is the least ugly, in my opinion. What do you think?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload