Featured Articles
Announcing Pi
Technical Specifications
Raspberry Pi FAQs
Win a Pi
Raspberry Pi Wishlist
Actions
Forum Thread Details
  • Replies 9 replies
  • Subscribers 668 subscribers
  • Views 1863 views
  • Users 0 members are here
Related

MySql and Php

nicolaoscon96

Hello there,

 

I have a question and its not on electronics. I am building a website (to using MySQL and PHP) in order to control various things around the house (IoT). I manage to connect the database to the website all good until here but my question is...I am gonna have and other people added to the database each one with his/her own credentials (my family for example). How am I going to keep each one's settings separate? Can i create a new column in sql for each (e.g toggle button I add in PHP?) Is it cleaver to add a new table for each user that is added> (to half answer this question> adding another table I am going to overpopulate the database and its going to be a nightmare for maintenance and performance.

 

Any suggestions on this one?

 

Thanks a lot

nicolaoscon96

NcCon

Parents

  • Hi Nicolas,

     

    I cannot imagine the database will grow much, even if you have multiple tables to support your needs. Multiple tables is a normal scenario. Databases (even on the Pi) can handle tens of thousands of entries with little performance impact, since you're not making so many queries per sec.

    So, I'd suggest design the table or tables to meet your needs, and efficiency and maintenance will likely not be an issue for a home IoT solution.  Once you have a table designed, a normal way (I'm not a DB expert, but this is the way I have done it in the past) you should be adding rows as required. So, you could (say) have a table of users, and you'd add a row as each new user is required or subscribes. User permissions could be stored in the row too. You could also have a table of devices perhaps.

    What might impact performance a lot is the actual web server. For a simple Pi solution, Lighthttpd is definitely preferred over Apache, it will run much faster with less resource usage.

Reply

  • Hi Nicolas,

     

    I cannot imagine the database will grow much, even if you have multiple tables to support your needs. Multiple tables is a normal scenario. Databases (even on the Pi) can handle tens of thousands of entries with little performance impact, since you're not making so many queries per sec.

    So, I'd suggest design the table or tables to meet your needs, and efficiency and maintenance will likely not be an issue for a home IoT solution.  Once you have a table designed, a normal way (I'm not a DB expert, but this is the way I have done it in the past) you should be adding rows as required. So, you could (say) have a table of users, and you'd add a row as each new user is required or subscribes. User permissions could be stored in the row too. You could also have a table of devices perhaps.

    What might impact performance a lot is the actual web server. For a simple Pi solution, Lighthttpd is definitely preferred over Apache, it will run much faster with less resource usage.

Children