Re: What's the worst that can happen with below database design? Immediately or in the future?
Posted by:
Ozgur Zeren
Date: September 05, 2014 12:40PM
Sorry, i should have spoken about how data models will work in php, and how will the entire thing function. It may have led to misunderstanding :
"Ouch. That compounds the complexity when generating queries."
"Ouch. That compounds the complexity when generating queries. "
"1. All access should go through a layer. It looks like you are versed in PHP; good. "
- Everything will indeed work through a single data object, which will map the tables from the data models the developers define in their module init. (a simple array). So not only all data is accessed solely through data access layer, but actually a single data object.
- There will be no access to the database directly. Developers wont ever have to interact with the database or generate queries themselves.
"Ouch. Someday you will need _another_ INT -- ALTER TABLE will be needed.
The code will allow for introduction of new tables after the format of existing ones, or new fields as necessary. Though, this would be minimized as much as possible.
"2. Think hard about what queries will be needed, and which fields will need to be indexed to make the queries efficient.
3. Throw the rest of the fields into a JSON structure. "
2 is totally dependent on what kind of module a developer may code. there is no telling that. but since the tables contain the fields necessary to link any relationship in between tables, they have good freedom of making models with good relations.
as for 3, the main reason why i am doing this avoiding json, array etc storage in database. which is something apps like wordpress do, and which is problematic for searches with multiple criteria.
"4. Think of "Entities" (id + a few indexed fields + plus JSON for open-endedness) and "Relations" (1-many, many-many; extra tables with rarely more than 2 fields). "
Im thinking that since i already provided placeholder fields for linking any table in any-to-any format, this would allow any developer to come up with their data model. the 'data_type' and 'attribute_name' fields will be linked with attribute id to provide a unique key which will help in various functions.
"Key-Value (EAV) schema does not scale. "
yes i am well aware of this. that is why this is not an eav schema, not even a hybrid - it is plain out relational, as in product to product_attribute relationship. (one to many)
the data_type and attribute_type fields are there not to create eav entries, but to allow identification of different data models of different modules.
this format exists in oscommerce's database structure even if it is not generic, but instead completely relational. and despite its 10 years old code, its handling itself quite well with tens of thousands of product (plus 2x product attribute entries) with 2+ complex joins and searches with multiple criteria.
for my purposes this is quite proper. the target is small to medium size applications which will probably be hosted in shared hosting to entry level dedicated servers. over that size the power of hardware phenomenally increases, so that would cover anything else for the most part.