What's the worst that can happen with below database design? Immediately or in the future?
Posted by: Ozgur Zeren
Date: September 04, 2014 08:44AM

Im wondering what's the worst that can happen with a database design like the one im entertaining below.

================================================================
Im designing a micro framework which is targeted at making module development easier. For that, i am trying to make a general db design which will be usable for majority of potential data models, without having the module developer need to create new tables for their models. This will also allow me to use a generic model class which will serve all kinds of requests over itself, thereby giving the MF engine control over the process and making engine development easier.

Here is the database schema - i tried to attach a screenshot from imgur but aparently that keyword is banned for some reason so i have to do it in plain text:



Main Table
-------------------

primary_key data_type field1 field2 field3 field4 field5 field6 field7
----------- --------- ------ ------ ------ ------ ------ ------ ------

Attribute Table 1
-----------------

attribute_key1 primary_key attribute_key2 attribute_key3 attribute_name field1 field2 field3 field4 field5 field6 field7
-------------- ----------- -------------- -------------- -------------- ------ ------ ------ ------ ------ ------ ------

Attribute Table 2
-----------------

attribute_key2 primary_key attribute_key1 attribute_key3 attribute_name field1 field2 field3 field4 field5 field6 field7
-------------- ----------- -------------- -------------- -------------- ------ ------ ------ ------ ------ ------ ------

Attribute Table 3
-----------------

attribute_key3 primary_key attribute_key1 attribute_key2 attribute_name field1 field2 field3 field4 field5 field6 field7
-------------- ----------- -------------- -------------- -------------- ------ ------ ------ ------ ------ ------ ------

Attribute keys of each table introduced to each other to allow for an inter-relationship in between tables.

Field types can be spread out in any format appropriate; Maybe two of them longtext, one of them bigint or one of them tinyint one of them varchar.

Naturally there will be numerous empty fields in tables for data models which dont use all of the fields. I will try to minimize the impact by using varchar with default value null for most text fields. And will try to minimize the impact of other types of fields. I dont think such size overhead will be too much impact where application will be used.

I dont know how i should distribute the fields though. How many Longtexts in a given table, or how many INTs or any bigint etc. I dont know a statistic to make such an estimation for covering most used field combinations.

Explanation of how it works :

data_type is the unique identifier of the data type. Say, Blog posts. primary_key is the key for the main identity (on top of the tree). Attributes are related to the main identity, and attached to the identity through primary_key.

As an example of how a module developer would use this, with a crude and simple example - it could work with any format that allows arrays, but for the sake of example :

Define an array which expresses a data model and maps it to coder-readable fields :

$var['models']['module_name']['blog_post'] = array( 'maintable'=>array('

'primary_key' => 'post_id',
'blog_post' => 'data_type',
'title' => 'field1'
'),
'attribute_table1' => array(

'attribute_key1' => 'category_id',
'primary_key' => 'post_id',
'field1' => 'tag'

),
);

With this, the module dev defined a 'blog_post' Data Model which consists of post id, title and attached tags, spanning two tables. Belonging to module 'module_name'.

From that point on, if we say the module_name is blog, what the dev needs to do to access this data object would be something like :

$data_access->get_data('blog','blog_post',post_id);

(Module name, model name, data id)
================================================================

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.