Multiple users question (for a newbie)

I'm in commercial aviation and I keep all my logbook in a MySQL
database. I've written some scripts to pull the data out in the
formats I like etc. but so far it's just a local system. Ideally, I'd
like to put it on the web and then let other people use it.

However, in my database there are 3 tables:
aircraft
airfields
maindata

The maindata one contains, as the name suggests, all of my flights,
with the other tables holding extended information about the aircraft
and airfields I use day-to-day.

If I were to let other people use this system, would I:
a) Create another maindata table for each user (called maindata1,2
etc. for example)
or
b) Just keep everything in one table and have a unique reference for
each flight entry?

I think a is the right answer, but if someone could tell me
definitely, I would be much obliged.
The other tables are shared data anyway so would be used by all.

Robin wrote:

> The maindata one contains, as the name suggests, all of my flights,
> with the other tables holding extended information about the aircraft
> and airfields I use day-to-day.
>
> If I were to let other people use this system, would I:
> a) Create another maindata table for each user (called maindata1,2
> etc. for example)
> or
> b) Just keep everything in one table and have a unique reference for
> each flight entry?

Much is up to your skills and what rights you have on the database.

The advantage with A is that each user can make changes (update/delete)
without it causing problems for the other users, if editing at the same time.

B has the advantage that it's a lot more easy to search for data, with locking
the table when making changes, would prevent that the data would be messed up
(this way you are at the level of A, but you need to do checks in your scripts
and retry after the table lock has been removed).

Most people would go with B, I think, but not everyone will be thinking of
locking the table.


--

//Aho

On 21 Apr, 16:21, "J.O. Aho" <u...@example.net> wrote:
> Robin wrote:
> > The maindata one contains, as the name suggests, all of my flights,
> > with the other tables holding extended information about the aircraft
> > and airfields I use day-to-day.
>
> > If I were to let other people use this system, would I:
> > a) Create another maindata table for each user (called maindata1,2
> > etc. for example)
> > or
> > b) Just keep everything in one table and have a unique reference for
> > each flight entry?
>
> Much is up to your skills and what rights you have on the database.
>
> The advantage with A is that each user can make changes (update/delete)
> without it causing problems for the other users, if editing at the same time.
>
> B has the advantage that it's a lot more easy to search for data, with locking
> the table when making changes, would prevent that the data would be messed up
> (this way you are at the level of A, but you need to do checks in your scripts
> and retry after the table lock has been removed).
>
> Most people would go with B, I think, but not everyone will be thinking of
> locking the table.

Thanks for the quick reply!
If, theoretically, my scripting skills were so amazing that, say,
20,000 people wanted to use my database, I suppose A would have to be
the method otherwise the data table would be locked out most of the
time and people couldn't enter their data?
Also if you are trying to keep data private, would it be possible for
anyone with read access to a single table to read everyone else's
data, by doing a select * query (or similar)?

Robin wrote:
> On 21 Apr, 16:21, "J.O. Aho" <u...@example.net> wrote:
>> Robin wrote:
>>> The maindata one contains, as the name suggests, all of my flights,
>>> with the other tables holding extended information about the aircraft
>>> and airfields I use day-to-day.
>>> If I were to let other people use this system, would I:
>>> a) Create another maindata table for each user (called maindata1,2
>>> etc. for example)
>>> or
>>> b) Just keep everything in one table and have a unique reference for
>>> each flight entry?
>> Much is up to your skills and what rights you have on the database.
>>
>> The advantage with A is that each user can make changes (update/delete)
>> without it causing problems for the other users, if editing at the same time.
>>
>> B has the advantage that it's a lot more easy to search for data, with locking
>> the table when making changes, would prevent that the data would be messed up
>> (this way you are at the level of A, but you need to do checks in your scripts
>> and retry after the table lock has been removed).
>>
>> Most people would go with B, I think, but not everyone will be thinking of
>> locking the table.
>
> Thanks for the quick reply!
> If, theoretically, my scripting skills were so amazing that, say,
> 20,000 people wanted to use my database, I suppose A would have to be
> the method otherwise the data table would be locked out most of the
> time and people couldn't enter their data?

If they try to edit at the same time (creating new data usually don't cause
any problems, it's when you edit/delete data that things can go really wrong).


> Also if you are trying to keep data private, would it be possible for
> anyone with read access to a single table to read everyone else's
> data, by doing a select * query (or similar)?

If you have everything in a table, then all who has read access to the table
can read everything anyone else has written (assuming they have direct access
to the database), but most systems has a frontend and it's in those cases the
frontend that checks who has access to what.

Just take a look at a portal like www.power.org, even if all the userdata is
stored in the same table, you don't have access to read other users data.

--

//Aho

Robin wrote:
> I'm in commercial aviation and I keep all my logbook in a MySQL
> database. I've written some scripts to pull the data out in the
> formats I like etc. but so far it's just a local system. Ideally, I'd
> like to put it on the web and then let other people use it.
>
> However, in my database there are 3 tables:
> aircraft
> airfields
> maindata
>
> The maindata one contains, as the name suggests, all of my flights,
> with the other tables holding extended information about the aircraft
> and airfields I use day-to-day.
>
> If I were to let other people use this system, would I:
> a) Create another maindata table for each user (called maindata1,2
> etc. for example)
> or
> b) Just keep everything in one table and have a unique reference for
> each flight entry?
>
> I think a is the right answer, but if someone could tell me
> definitely, I would be much obliged.
> The other tables are shared data anyway so would be used by all.
>

Keep everything in one table.

Comments about everyone having read access to it are immaterial. You're
only going to have one MySQL user (or maybe two) accessing the table
itself - that's going to be the one the script uses. And it will have
access to all tables, anyway.

For instance - you don't think Amazon creates a separate table for every
person's shopping cart, do you?

The way to do this is to have a user id column in your maindata table,
and a user's table. The user's table has an internal id, their login,
(encrypted) password and other info you want.

Then use your scripting language to keep people from altering (and
displaying, if you wish) data that isn't theirs.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

>I'm in commercial aviation and I keep all my logbook in a MySQL
>database. I've written some scripts to pull the data out in the
>formats I like etc. but so far it's just a local system. Ideally, I'd
>like to put it on the web and then let other people use it.
>
>However, in my database there are 3 tables:
>aircraft
>airfields
>maindata
>
>The maindata one contains, as the name suggests, all of my flights,
>with the other tables holding extended information about the aircraft
>and airfields I use day-to-day.
>
>If I were to let other people use this system, would I:
>a) Create another maindata table for each user (called maindata1,2
>etc. for example)
>or
>b) Just keep everything in one table and have a unique reference for
>each flight entry?

Making table names (or parts of them) reflect a piece of data is
almost always a mistake. (There might be an exception for security
issues if it is necessary for the user to directly access MySQL,
but that doesn't apply here.) Add a column for user name. Any
unique indexes will probably include the user name, or they'll be
an auto-increment key unique across the whole table. Is there any
reason for a user to legitimately access data that's NOT his? (For
example, general stats on recorded flights by airport? Stats on
types of planes used?) That's much harder with separate tables.

>I think a is the right answer, but if someone could tell me
>definitely, I would be much obliged.
>The other tables are shared data anyway so would be used by all.

Since you are providing access to this data via the web, only one
MySQL user (the one used by the web site) is likely to access MySQL.
You create web users via another table (the "users" table) and your
web page does user authentication. Most queries will qualify the
results by user.

Locking the table should not be a big issue. If I understand your
model, only one user should be allowed to edit any given record,
and if you only let each user log in once, there will be no conflicts.
You shouldn't lock the whole table: use transactions. And you
shouldn't attempt to hold a lock on a record between displaying a
record on a web page and submitting changes to it: that can cause
a complete lockup of your system even if it has only ONE user when
your OS crashes or your ISP connection drops or the user goes on
vacation.

Robin schrieb:
> If, theoretically, my scripting skills were so amazing that, say,
> 20,000 people wanted to use my database, I suppose A would have to be
> the method otherwise the data table would be locked out most of the
> time and people couldn't enter their data?

No, locking is usually done on a by-row level.
If you use transactions with the proper "isolation level", the database
will automatically do all the locking for you. (You'll need InnoDB for
that.)

> Also if you are trying to keep data private, would it be possible for
> anyone with read access to a single table to read everyone else's
> data, by doing a select * query (or similar)?

Only if you allow them to issue SQL queries directly.
Most of the time, you'll present them a web frontend and take care not
to leak data that they shouldn't see.

You can give each user specific rights for each table; however, mysql
doesn't seem to be optimized for that use case, as the manual warns that
the database will slow down if you use anything but per-database
permissions. (I think it needs extra queries in the permissions tables
for each query that the frontend issues.)
OTOH letting mysql doing the permission checking has the advantage that
you have a whole lot less to worry about security-wise, and if you do
the permission checking yourself, you'll incur just the same overhead on
the application side.
OT3H creating tables from a web frontend means you have to be
extra-careful with that particular operation, to avoid that some
anonymous nobody can fill your harddisk with almost-empty tables. And
you'll have to make sure that the per-user tables don't produce name
collisions with the shared tables - easy enough with the three tables
that you have right now, but can become quite a problem when you have
two dozen or more tables.

Regards,
Jo

On 21 Apr, 20:30, Joachim Durchholz <j...@durchholz.org> wrote:
> Robin schrieb:
>
> > If, theoretically, my scripting skills were so amazing that, say,
> > 20,000 people wanted to use my database, I suppose A would have to be
> > the method otherwise the data table would be locked out most of the
> > time and people couldn't enter their data?
>
> No, locking is usually done on a by-row level.
> If you use transactions with the proper "isolation level", the database
> will automatically do all the locking for you. (You'll need InnoDB for
> that.)
>
> > Also if you are trying to keep data private, would it be possible for
> > anyone with read access to a single table to read everyone else's
> > data, by doing a select * query (or similar)?
>
> Only if you allow them to issue SQL queries directly.
> Most of the time, you'll present them a web frontend and take care not
> to leak data that they shouldn't see.
>
> You can give each user specific rights for each table; however, mysql
> doesn't seem to be optimized for that use case, as the manual warns that
> the database will slow down if you use anything but per-database
> permissions. (I think it needs extra queries in the permissions tables
> for each query that the frontend issues.)
> OTOH letting mysql doing the permission checking has the advantage that
> you have a whole lot less to worry about security-wise, and if you do
> the permission checking yourself, you'll incur just the same overhead on
> the application side.
> OT3H creating tables from a web frontend means you have to be
> extra-careful with that particular operation, to avoid that some
> anonymous nobody can fill your harddisk with almost-empty tables. And
> you'll have to make sure that the per-user tables don't produce name
> collisions with the shared tables - easy enough with the three tables
> that you have right now, but can become quite a problem when you have
> two dozen or more tables.

Thanks for all the replies, everyone. Looks like I've got a lot of
reading to do (InnoDB? Not even sure what table type I've got). One
table seems to be the consensus then, without letting people write
their own queries

Robin