0

I have a database with several tables. There are several user accounts that should be able to CRUD their stuff but should never see other users information. I was planning to store the UsersID in every record he has access to and then make sure that every database call has this "criteria" implemented.

But this seems to contradict the general rules of database abstraction. Is there a better way? The users will never need to see anyone else's data. I will require users to create a login.

Thanks, Pascal

Tate83
  • 274
  • 1
  • 7
  • 21

1 Answers1

0

It isn't clear which database you are using. I guess it is either MySQL or SQLite, because these are the most frequently used database engines with LiveCode.

It is quite normal to add the ID of the user who creates the record in a database. Normally, you would have another, separate table with the same ID's and information about the users. That would be mainstream database design and it wouldn't contradict any rules. Moreoever, rules exist to be broken.

You can try to add a trigger to your database, which creates a subset of the data by selecting all records with the current user ID, before running a query.

Mark
  • 2,380
  • 11
  • 29
  • 49
  • Yes I was thinking SQLite but might upgrade to MySQL after development. I have a users table with information on them. However, I think I probably should have the Userid in every record this user can access to enforce this separation. Triggers sound like a good way to do this, I will look into it. – Tate83 Apr 30 '14 at 09:55
  • If you have groups of users, you can include a group number in the records in the users table and include the same numbers in the other tables. There are just different ways to do it. Database design isn't rocket science, you might say --although launching rockets is actually one of the crudest acts by science I know of. – Mark Apr 30 '14 at 21:53
  • 1
    Ok, that would be another way. Glad to know I'm not too far off with my idea so I'll find a solution. Probably I'll just add the UID to the tables since in this case 1 user = 1 group. However, the group solution might be more flexible for future ideas.. Thanks for your input. Rocket science.. We have seen how bugs in rockets can affect the whole mission terribly.. – Tate83 May 01 '14 at 11:22