I’ve got a mobile phone fanpage. I want to rewrite it from scratch with Yii. At the moment I have a database table called “mobiles” where I store the properties of each mobile phone. Currently in the database there are fixed table columns, like “phone name”, “phone size”, “camera”, etc…
Since properties come and go (as new technologies appear) I want to make it more dynamic. So that I could add custom columns to the database from an admin panel in my application.
I have a couple ideas on how to implement such a thing, but I’m not really sure which is the best way to do it. I wonder about your opinion.
I’d personally have a separate table for features and a further table linking features to phones. This way, you can make the properties customisable without having to alter your schema.
As of the structure of the data I would think about if a MySQL table is the right tool here.
The nature of MySQl is to have somewhat fix columns and tables. I would suggest to think about maybe using Mongo, Couch or some other schemaless Storage.
If you want to stick with MySQL here I would suggest the following:
Phone table with general stuff all phones have
Feature table with specifications regarding the nature of the feature, e.g. Unit
A (relational) feature value table with: phone_id, feature_id, value
Than you’ll be able to connect the Phone & Feature table with a through relation like so:
Thank you for the replies. I’d rather stick to MySql as that’s what I know.
As far as I’m not mistaken Keith and rhomb suggests the same thing (3 separate tables: phones, features and one which connects them to each other).
Well, there’s no such thing as “general stuffs that all phones have”. Because I want to display all features for all phones, even the ones which a phone doesn’t have. For example I want the “Wifi” feature displayed in the older phones profile as well, showing that it does not support wifi.
If I’m right, binkabir suggests a singe table solution (phones table) which would have all the features in it as columns and when a new feature comes I would alter the table every time. Since new features doesn’t come so often, I can also imagine it as a good solution. However the other way seems more “professional” to me. What do you think?
Also feature types are different. As for “Wifi” feature I’d add a bool type column, while for “phone name” I’d use varchar, and so on… How would you implement this if not using the query builder method?
If features will only ever be added, the single table solution is viable. I assume you’d have defaults set up so that existing records receive a suitable value for the property.
In general I’d prefer to avoid schema manipulations for this sort of thing. Changing the schema dynamically means that the query you would use to initially set up your database would become stale and your database schemas between your test and live servers would diverge. If you’re using a model, you’ll need to update the code files to account for the new fields every time, which destroys the dynamic nature of the schema update.
Using separate tables seems like a cleaner solution, but it’s not without its drawbacks. Firstly, you won’t have database level control over the type of data stored. Probably the best you can do is have a field representing the data type and keep the value of the property in a text field. Your PHP code would have to parse the value based on the specified data type.
You can set up a class hierarchy to manage the typing cleanly, but it’s probably overkill for your scenario.
The solution Keith suggested can be interpreted the same way as mine… just thought to mention that the values of the features should be stored in the relational table.
Database validation is actually a drawback that I didn’t consider. A way to implement this would be over a behavior calling validators depending on the feature table. The features table would look sth like: id, name, validator
But like Keith already stated, depending on how big your Project is that might be overkill.
If you already know that this project will be taking the feature information based on public user input you might want to take this route though.
Well, after all, I’m still considering just leaving it as it is now (1 static table) and whenever a new feature comes in I’d add it manually and update the code as well. Just because new features come very rarely (once or twice a year, maximum) and all of the solutions have some drawbacks.
