File Vs Database

Question

I'm going to implement an Access Control List for each individual user so they can assign access to their own resources so they can hide stuff, for example, from their mothers, but show their friends.

Now storing ACL in a database seems like it can get pretty insane when each user is also a group, which can have many sub groups. So I'm thinking of storing the ACL stuff in a text file.

Good Idea? Bad Idea?

EDIT: I should note, I'm talking about an individual text file for each user. I'm thinking of creating an ACL class which I could serialize and write to the text file. My fear is that storing the ACL in a database would create insanely huge join tables and put a significant strain on the database server.

Answer 1

I'm not sure how it's any easier storing it in a text file. The relationships of the data are the same. Granted, SQL is not always friendly hierarchical data, but neither is a flat text file.

Answer 2

Is your textfile protected against concurrent access? Else a database will be a better idea.

Answer 3

XML is ideal for hierarchical data. It's possible to work with hierarchical data in a database though, this explains how beautifully (the concepts apply not only to MySQL):

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Personally I wouldn't store data like that in a text file. The manipulation becomes a lot more difficult.

Answer 4

Concurrency will bite you. As will complexity and decreased performance as the file gets larger. As will the increased risk of corruption of something happens during writing of the file like power loss of system crashes.

A database will generally shield you from these issues and leave you to concentrate on the logic

You can implement hierarchical storage in a database using self joins e.g

ItemID    Data   ParentID
--------------------------

Where ParentID is a pointer to ItemID of a different row

Answer 5

Given the option I would store ACL in a database:

• The language to access and query the data is easier and standard (SQL)
• The DB will give you transactions, indexes for fast query, integrity constraints and security.
• Depending on how you store the data in a local file you may need to move your data files along with your application. Ex: Moving application from server1 to server2.
• For data that is immutable (or don't change often) some form of cache should be used. So, independently if you're using File or DB, you should be caching some of this data for some period of time.
• I'm pretty sure you can find good ACL schemas templates for relational database that you can use as a reference, like this document here: http://edhs1.gsfc.nasa.gov/waisdata/v2r20/ps/cd31110001.ps

I hope it helps.

Answer 6

My fear is that storing the ACL in a database would create insanely huge join tables and put a significant strain on the database server.

With one text file per user, and your groups/sub-groups presumably in some other files, you'll have to roll your own joins, won't you?

The problem/requirement you have described is exactly what SQL is good for. SQL is the right tool for the job, no question.