Blesta Addons Posted April 21, 2015 Report Share Posted April 21, 2015 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 ? PauloV 1 Quote Link to comment Share on other sites More sharing options...
serge Posted April 21, 2015 Report Share Posted April 21, 2015 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 Quote Link to comment Share on other sites More sharing options...
LukesUbuntu Posted April 21, 2015 Report Share Posted April 21, 2015 i think you should create a basic ERD so it would be more clearer for your requirements Quote Link to comment Share on other sites More sharing options...
Blesta Addons Posted April 21, 2015 Author Report Share Posted April 21, 2015 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 .... Quote Link to comment Share on other sites More sharing options...
Blesta Addons Posted April 21, 2015 Author Report Share Posted April 21, 2015 i think you should create a basic ERD so it would be more clearer for your requirements Thanks . Quote Link to comment Share on other sites More sharing options...
Michael Posted April 21, 2015 Report Share Posted April 21, 2015 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 Quote Link to comment Share on other sites More sharing options...
Blesta Addons Posted April 21, 2015 Author Report Share Posted April 21, 2015 Maybe the marketplace will show this it was just a example , my work has nothing related to blesta release Quote Link to comment Share on other sites More sharing options...
serge Posted April 21, 2015 Report Share Posted April 21, 2015 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:"";} ---------------------- Quote Link to comment Share on other sites More sharing options...
Tyson Posted April 21, 2015 Report Share Posted April 21, 2015 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 Blesta Addons 1 Quote Link to comment Share on other sites More sharing options...
mrrsm Posted April 21, 2015 Report Share Posted April 21, 2015 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 1 Quote Link to comment Share on other sites More sharing options...
Blesta Addons Posted April 21, 2015 Author Report Share Posted April 21, 2015 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 PauloV 1 Quote Link to comment Share on other sites More sharing options...
Tyson Posted April 22, 2015 Report Share Posted April 22, 2015 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. Blesta Addons and PauloV 2 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.