What is the best table structure for use with PHP?

[bakertaylor28]

What is the best table structure when constructing what will be a  potentially large multi-user platform?

Example- Each user inputs their own individualized information into a table, for recall only to that specific user and to certain other users defined as administrators or half-administrators super users.

Would it be better to store this all in a single table, or to give each user their own individual table on formation of the account?

[requinix]

54 minutes ago, bakertaylor28 said:

Would it be better to store this all in a single table,

Probably. Going to need to hear more about it than just how it's "individualized information".

 

54 minutes ago, bakertaylor28 said:

or to give each user their own individual table on formation of the account?

Definitely not.

[bakertaylor28]

10 minutes ago, requinix said:

Probably. Going to need to hear more about it than just how it's "individualized information".

 

Definitely not.

The application I'm building issues an account and then basically stores a set of property information (such as make/model, serial number, etc.) to the individual account and allows the user to recall the information in his own account but not in other user's accounts. It also will allow "marking" certain property missing, etc. to display to super users (e.g. people manually verified to have a right to know the info and then granted a superuser status) using an administrative "level" model , while the system administrator has access to all of the information everywhere.

My first instinct is to give each user their own table in the same database (which will be a seperate database from login credentials) in order to keep a while loop from having to cycle through like half a million rows to select only the pertinent info. in that displaying a whole table is more straight forward than using a WHERE statement.

Edited September 4, 2020 by bakertaylor28

[requinix]

1 hour ago, bakertaylor28 said:

The application I'm building issues an account and then basically stores a set of property information (such as make/model, serial number, etc.) to the individual account and allows the user to recall the information in his own account but not in other user's accounts.

Is it the same set of information for everyone?

 

1 hour ago, bakertaylor28 said:

My first instinct is to give each user their own table in the same database (which will be a seperate database from login credentials)

Definitely do not do this. No offense, but it doesn't sound like you understand what databases do or how they work.

 

1 hour ago, bakertaylor28 said:

in order to keep a while loop from having to cycle through like half a million rows to select only the pertinent info.

Thing is, you would never actually do that. You would run a SELECT query with a WHERE clause. And the system will be able to pull up the information far more efficiently than you could if you tried to do it yourself.

[bakertaylor28]

5 minutes ago, requinix said:

Is it the same set of information for everyone?

 

Definitely do not do this. No offense, but it doesn't sound like you understand what databases do or how they work.

 

Thing is, you would never actually do that. You would run a SELECT query with a WHERE clause. And the system will be able to pull up the information far more efficiently than you could if you tried to do it yourself.

The idea is that by pulling data from a smaller table in which all the data would be used (e.g. given a SELECT * from table argument)  would be faster and more efficient than having to use the form of Select * from table WHERE parameter1=x and parameter2=y   type of argument.) because it would seem that the latter takes much more time and resources to execute, and would be more secure because we're partitioning tables.

[bakertaylor28]

1 hour ago, requinix said:

Is it the same set of information for everyone?

 

Definitely do not do this. No offense, but it doesn't sound like you understand what databases do or how they work.

 

Thing is, you would never actually do that. You would run a SELECT query with a WHERE clause. And the system will be able to pull up the information far more efficiently than you could if you tried to do it yourself.

To clarify, it would be the same table structure for everyone, but would be different user-supplied data for each account, in a setting where data integrity matters, forensically speaking. It would seem that by providing the account with it's own database table, it then, worst case, minimizes the impact of SQL injection- because the injector then only theoretically would effect only one particular table Vs. the potential for wiping out the whole database (and thus effecting the integrity of the whole SQL server) if everything is stored in one table. The idea is essentially de-centralizing vs. having a central table.

Also, correct me if I'm wrong, but It would seem that using a WHERE clause would take much more server-side resource than using a SELECT * clause, given a heavily populated data set with a WHERE clause (given the nature of the thing)  versus a much less populated data set with a SELECT * clause where each account has it's own table. The real goal is to isolate the data as much as possible (to where potential attacks effect a minimal data set), while still making it accessible to the superuser and administrator accounts.

For example, if someone were able to inject a DROP TABLE command, if I have the data isolated into several tables, it only effects minimal data, vs. if it's all in one table, then everything gets dropped. This could buy more time to perhaps shut down a server to stop an attack.

Edited September 4, 2020 by bakertaylor28

[kicken]

2 hours ago, bakertaylor28 said:

minimizes the impact of SQL injection

If you have a SQL injection vulnerability, separate tables will not necessarily help you.   Depending on the kind of vulnerability / setup you have the attacker may be able to run queries against arbitrary tables, or just drop the entire database.

Aside from that, separate tables will become a maintenance nightmare when next year you need to add a few extra fields to the schema for all 10,000+ tables or whatever.  Or you decide you want to be able to aggregate the data for a report and have to query every table and combine the results.

Multiple tables with the same schema is the wrong solution 99.999% of the time, and you're not in that small 0.001%.

2 hours ago, bakertaylor28 said:

Also, correct me if I'm wrong, but It would seem that using a WHERE clause would take much more server-side resource than using a SELECT * clause,

Your wrong.  Databases are designed to process data.  They are designed to deal with tables that have many rows quickly and efficiently so long as you set them up properly.  As an example, I have a table that records every login attempt to a website.  It currently has about 2.2 million rows in it.  I just queried it to find all my login attempts.  It came back with 2976 rows.  Guess how long it took the database to find those records out of the 2.2 million total records?  0.271 seconds.  Yes, less than a second.

2 hours ago, bakertaylor28 said:

For example, if someone were able to inject a DROP TABLE command

If they can inject a DROP TABLE command, they can probably inject a SHOW TABLES command to get a list of all your tables then loop that list and drop each one.  Your approach buys you nothing.

You need to make sure you're not vulnerable to an injection attack in the first place, and then have solid backups to restore from just in case something does happen.

 

Edited September 4, 2020 by kicken

[bakertaylor28]

17 hours ago, kicken said:

If you have a SQL injection vulnerability, separate tables will not necessarily help you.   Depending on the kind of vulnerability / setup you have the attacker may be able to run queries against arbitrary tables, or just drop the entire database.

Aside from that, separate tables will become a maintenance nightmare when next year you need to add a few extra fields to the schema for all 10,000+ tables or whatever.  Or you decide you want to be able to aggregate the data for a report and have to query every table and combine the results.

Multiple tables with the same schema is the wrong solution 99.999% of the time, and you're not in that small 0.001%.

Your wrong.  Databases are designed to process data.  They are designed to deal with tables that have many rows quickly and efficiently so long as you set them up properly.  As an example, I have a table that records every login attempt to a website.  It currently has about 2.2 million rows in it.  I just queried it to find all my login attempts.  It came back with 2976 rows.  Guess how long it took the database to find those records out of the 2.2 million total records?  0.271 seconds.  Yes, less than a second.

If they can inject a DROP TABLE command, they can probably inject a SHOW TABLES command to get a list of all your tables then loop that list and drop each one.  Your approach buys you nothing.

You need to make sure you're not vulnerable to an injection attack in the first place, and then have solid backups to restore from just in case something does happen.

 

That helps my understanding of it quite a bit. (I'm quite new to SQL and have only done a modest bit of PHP- This is my first project from scratch.) . So the best structure, If I'm understanding it, is to combine everyone's data into the same table and use a WHERE statement. I'm also guessing that  you can use WHERE  column A=something AND column B = something else AND  column C= something else (going as many columns as need be) if necessary? I'm having a hard time finding examples of SQL queries within the context of PHP that actually work.

Edited September 5, 2020 by bakertaylor28

[maxxd]

21 hours ago, bakertaylor28 said:

I'm having a hard time finding examples of SQL queries within the context of PHP that actually work.

MySQL queries are MySQL queries, regardless whether they're run in PHP, Ruby, MySQLWorkbench, or the command line. Unless you're using an ORM like Doctrine or Eloquent, it's all the same keywords and operations.

But yes, you can have multiple criteria in a WHERE clause rather easily. Also, please keep in mind that doing a SELECT * is less efficient than selecting the fields you need specifically.

[bakertaylor28]

On 9/5/2020 at 5:27 PM, maxxd said:

MySQL queries are MySQL queries, regardless whether they're run in PHP, Ruby, MySQLWorkbench, or the command line. Unless you're using an ORM like Doctrine or Eloquent, it's all the same keywords and operations.

But yes, you can have multiple criteria in a WHERE clause rather easily. Also, please keep in mind that doing a SELECT * is less efficient than selecting the fields you need specifically.

 

I get that part, but the syntax is different in how it's deployed depending upon the language you're using.

[maxxd]

I'm not sure I understand - we're in the MySQL Help forum, so are you having trouble with the SQL statement or the language you're using to run the statement? For instance, in PHP whether you're using PDO or MySQLi to interact with MySQL, or PDO or pg_* functions for PostgreSQL, the SQL is (close enough to) the same, but yes, the PHP running the statements will be very different. However, the PHP is a very different thing from the SQL and it's important to know which you mean.