MySql and Php

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.

Hi nicolaoscon96 . I work with mainframe tables.To a degree my colleague is correct. A good design follows:

1. Table named "users" with a column keyed with "username". The next columns are "permission", "active", etc
2. Table named "device" with a column keyed with "devicename". The next columns are "attributes", "type", etc.
3. Table named "joined-user-dev" with column keyed with "number". It auto increments. Columns named "username" and "devicename"

To use this structure you join #1 with # 2 and #3 above to list all the relationships. If this sounds complicated, I suggest you read http://www.dummies.com/programming/sql/how-to-design-a-sql-database/

Its not complicated at all. I finished my computer science degree and am more into databases ..i know my way around. Thanks for the answer

I will try it...thanks a lot for the answer

Ah. Then you see I did a many to many with #3 in the middle.

if setings are per user, you can add a user_id column to each table that has user_dependent values.

When inserting a new setting/record for the user, fill in the user_id.

When updating or removing a setting, use the user_id in the where clause.

Here is another idea:

A users table, columns Id, Username, and User's name. Your choice if you need the name in multiple columns (first name, middle, last) or just one. It might be overkill, but other fields can be used to further describe the user, (gender, address, DOB, etc.) The username could be an email address or a unique name that the user chooses for him/herself to log in.

Settings table, columns Id and setting description, such as Password, Color theme, Status (active, disabled), last log in date/time, etc.

User_Settings table, columns id, user id, settings id, value

Good luck with your project! sgarcia

Adding and manage items with PHP and MySQL is not a nightmare, if you know what you are doing, and the table size will be kept low.

Consider designing the tables before creating them, to better understande how to access the records. Also, to keep things quick, remember that indexing is your friend.

Many here gave you possibe parts of the final solution. What I can add is: always mind security when juggling with databases and IoT.

1. Always change default passwords
2. Use variables in your queries
3. Sanitize queries (i.e. check return values before using them)
4. Use secure wireless networks (possibly WPA2)

And please, keep us informed about your progress: we might even help you write down a tech post.

Luigi

There are some useful tools, like the MySQL console running on both Windows and OSX (I think on Linux too) platforms. This gives you the possibility to design graphically the database, create your queries and test the consistency of the entire system. The same tool can be connected to both local and remote databases to organize tables, queries, static queries and all, as well as producing a graphical view of the database and the various relations between tables. This is a local tool very useful when creating a new database from scratch also because you don't need to stay always connected with the remote web server. I use it for custom database designs. When the database has been created you can move to the online version managing it with phpMySQL that gives a lot of features also for data extraction, data upload (accept xml, csv, excel format, and text only). With PhpMySQL it is also almost easy to create several permissions (without the need to create the corresponding users in the Linux environment).

Indeed, if you are basing your architecture on wordpress, that I strongly suggest also if your web site has nothing to do with the WP themes and plugins, if you have already (you need to) the suite Apache2 + MySQL + PhpMySQL the WP installation also creates its own standard database that can then be updated/upgraded with custom tables, special permissions and more. Frankly, based on my personal experience I always prefer to create a separate custom DB associated with the standard WP database to manage this case of complex data architectures.

Enrico