Table Structures For New Plugins

[Blesta Addons]

Hello All,

i have a custom work to do , but i need your seggestion before i begin , we need to store apps details in tables , but every apps can have new version in the future , so we need when we update a existing app version in apps table , we want to let trace for old versions , like downlods, views, ratings ....

the apps table will have a ID for each app, this id si AUTOINCREMENT , also the app name is unique so we can't add the same app twice in the apps table .

i know we can need to use two table "apps" and "data" , one for apps , and other for data (downs, views, rates) ... in this case if we have 4 app , we will have 4 rows in data , when we release new version of 2 apps, we still have 4 rows in apps table , and we will have 6 rows in "data" table .

this is a good structure or thier is a other structure methode that i should use ?

what is your suggested shema for this ?

[serge]

not a specialist but here my idea:

 

only one table,

 

having 4 columns:  id (primary key) / app_id / field / value

 

 

so in "field" column you will have something like:

app_version

app_name

app_this_data1 (you will choose name that really match to your data type)

app_this_data2

etc

 

so it's flexible as you create any kind of "field"  you need, and the "value" column can be "textarea" format so you can even store very long string if you need,

 

I saw this type of storage in Magento, wp, etc

[LukesUbuntu]

i think you should create a basic ERD so it would be more clearer for your requirements

[Blesta Addons]

not a specialist but here my idea:

 

only one table,

 

having 4 columns:  id (primary key) / app_id / field / value

 

 

so in "field" column you will have something like:

app_version

app_name

app_this_data1 (you will choose name that really match to your data type)

app_this_data2

etc

 

so it's flexible as you create any kind of "field"  you need, and the "value" column can be "textarea" format so you can even store very long string if you need,

 

I saw this type of storage in Magento, wp, etc

Thanks Serge ,

your solution was our main solution in the first .

but the client has a condition that the every app should has only one row , so no duplication of app in the table . also we need detailled data about every version of the app . like

blesta v2.5 , 3000 ownloads, 700000 views

blesta v3.0 , 20000 ownloads, 900000 views

blesta v3.3 , 50000 ownloads, 950000 views

blesta v3.5 , 50 ownloads, 500 views

....

[Blesta Addons]

i think you should create a basic ERD so it would be more clearer for your requirements

Thanks .

[Michael]

Thanks Serge ,

your solution was our main solution in the first .

but the client has a condition that the every app should has only one row , so no duplication of app in the table . also we need detailled data about every version of the app . like

blesta v2.5 , 3000 ownloads, 700000 views

blesta v3.0 , 20000 ownloads, 900000 views

blesta v3.3 , 50000 ownloads, 950000 views

blesta v3.5 , 50 ownloads, 500 views

....

 

Maybe the marketplace will show this

[Blesta Addons]

Maybe the marketplace will show this

it was just a example , my work has nothing related to blesta release

[serge]

but the client has a condition that the every app should has only one row , so no duplication of app in the table . also we need detailled data about every version of the app . like

Ok, so I will say, you could use serialized way, wp is using that way also:

 

so it could be 4 columns:  id (primary key)  / app_id / app_version / app_name / data

 

so in "data" column (texarea format) you use serialized way  like :

 

-----------------

a:14:{s:7:"enabled";s:3:"yes";s:5:"title";s:46:"Dragonpay | Secure Payments in the Philippines";s:11:"description";s:105:"Pay over-the-counter with Cash Payments (you will receive email instruction from Dragonpay) OR Pay Online";s:11:"license_key";s:16:"gggggggggggggggg";s:14:"license_status";s:5:"valid";s:18:"license_public_key";s:272:"-----BEGIN PUBLIC KEY-----

MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDA99fZAvRTsSMQR6qIFgT9PahD

elWG6DR/oHhDVZkToZAbJJxTfeyaRSGARgu8eFq03LqFnJzB/C0uQ6hmxX1e5chm

uWRTQbTuGnKNNUV9hFq8uGaJcXwl9/wcxcfrFr+JORo1jDmQQM+bMapBPi619jQX

ToKV8yar5K4DmSS14QIDAQAB

-----END PUBLIC KEY-----

";s:12:"license_data";s:985:"v8Nza2+rrrdKt5Dcr4nGFnbpNWQQbNGuMLgLnYnZdOiLLrUp9KXRueDYQAdzLHKI8E3ZRLXPXU0999k6xPo2YK+BEGl9bWu03Rsnqceg1SzaSKCr27d1BiQHvsG1lyn8+l1ns6SUk6gElbMKfrfhgqz09olpkMIkTOYj8/kg0XCU/DByy2AqSXXrBSj27xYJEwQ4FT3X1fAs2l532FnqdkKQJ8Uwjb7UdVdEuh4Uqfd3Eac9p1UsQumwFC0SfktXQD5qvRifZoOcrrSP/GD4XZaSffjJ098sCBrTZJxRdjoIwTgHa5dNrWbBUBYfFz8HISxrk26xM9xoTTyvszpaiN3WTezKT69B1J81gXlAS8CL2XnHoSoieSB+XnwOdW20K78UxneLqb3Bwin0Qp7bMXjt3gprF4incipcGpNwP+XHezKRRFYV40RPEKz0x3/1OvaD+3ktxEx3rjjpV0/u/xZQGNxQdQ4GaipDbxUGGHAm/NgbQDEv33/Olz7gUtxq6fOO9pK/Gc8LqkHG2HNaCudvnxyZ9Xy+hMXrMfZrzWdUAH4Abtu+Si6oagH5UoDiwnhY9vvs28NfwSz/hRdbAsnt5JvGSrqLXd1IvvL3nxyMpA/QggtiEVDbBlE7vavRhjDe0pYiL//gl91CL+JA6N5gSqGn8VBue9YU7amlVqJve1g0xnn4dcB4vc6wXANzxUBJeTFPuYAoKr2PWPyXncYYAZ0XYbfo2UND+74R104eWLGkO7vHQ4fJ0JB7KeITNZlCZOsm16KHgOOUlJv/d6UTGQclt6jOA5Co7S143Ek=$p7fTw9F0al4Lm+3lIDsxOxskl4O5wMN9zRoVK16MaEFlIjdQpHPeLzHTBu1IUWvuMWIhDthaP63d6ftv2UK7H14DPKpsQZLF/8um67W4FcB2rCjvnEsHwzrj04+cvIAIv7Art1SZkgQjPlhBmCizS45P97qtJqF+SVuRBbfWWEw=";s:11:"merchant_id";s:10:"yyyyyyyyyyy";s:22:"dragonpay_api_password";s:8:"xxxxxxxx";s:9:"test_mode";s:3:"yes";s:5:"debug";s:3:"yes";s:16:"shopping_cart_id";s:0:"";s:18:"display_return_url";s:0:"";s:20:"display_postback_url";s:0:"";}

----------------------

[Tyson]

Serialized data isn't searchable.

 

As mentioned, if you're unsure, create an ERD to get a better idea.

 

Based on what you described, I would imagine something like:

`applications`
id, name, attr1, attr2, attr3, ..., attrn

`application_versions`
application_id, version, date

`application_views`
application_id, number_views, number_downloads

`application_ratings`
application_id, rating, count

[mrrsm]

What Tyson said seems like it would be what you wanted.

 

You may want to change the application_id in the `application_view` and `application_ratings` to be an id (added column) from the `application_versions` if you want to have statistics for each individual version

[Blesta Addons]

Serialized data isn't searchable.

 

As mentioned, if you're unsure, create an ERD to get a better idea.

 

Based on what you described, I would imagine something like:

`applications`
id, name, attr1, attr2, attr3, ..., attrn

`application_versions`
application_id, version, date

`application_views`
application_id, number_views, number_downloads

`application_ratings`
application_id, rating, count

thanks tyson , but your shema has a limitation , the count views, downloads , rating will be for the app and not for every version , also i have forget to say that the application table should have app name and app version .

what i will do is something like :

`applications`
id, name, version , attr1, attr2, attr3, ..., attrn

`application_versions`
application_id, version, date, number_views, number_downloads

`application_ratings`
application_id, rating

[Tyson]

thanks tyson , but your shema has a limitation , the count views, downloads , rating will be for the app and not for every version , also i have forget to say that the application table should have app name and app version .

what i will do is something like :

 

`applications`
id, name, version , attr1, attr2, attr3, ..., attrn

`application_versions`
application_id, version, date, number_views, number_downloads

`application_ratings`
application_id, rating

 

 

Since you want stats available for each version, I might revise it to:

`applications`
id, name, current_version, attr1, attr2, attr3, ..., attrn

`versions`
version_id, application_id, version, date, views, downloads

`version_ratings`
version_id, rating, count

Depending on how your ratings will be setup, you might change that, but I was assuming something like a 5-star rating. So each version would have 5 records, one for each rating, and each time someone gave it that rating, you increment count on that record.