2

I'm basically making a database of databases. An administrative User can go in and hit "Create Database" (which is basically a table) and set up the column types and how many columns. Other users can then go in and, through an interface, add entries to the database. The point is to create a management system for information within the company. As in, the administrator could create a "Employ" database to store workers with the correct columns and such.

I have it in my head that I should never let users create new tables (which is what a "database" would be) or even columns. I created two tables to store the information that defines what a database/table should be from the users perspective, and two more tables to store the entries and data for those entries.

I feel a bit like I'm reinventing the wheel. Is it ok just to let users add new tables.

1 Answer 1

7

Database design is both an art and a science. End users usually understand neither the art nor the science. I don't let end users add tables or alter tables; I've never worked anywhere that allowed anything like that.

End users will almost invariably build things that look like either Word documents or Excel spreadsheets. They will only rarely build things that look like databases.

The point is to create a management system for information within the company.

That's what a database is for. If your database is basically a table, as you seem to be saying, I think your users would be better off with a database.

As a database designer, I find these features to be indispensable. I'll give you the SQL keywords and concepts; you can look them up with Google if you like.

  • CHECK constraints
  • GRANT and REVOKE
  • Updatable views
  • Materialized views
  • Stored procedures
  • Foreign key constraints
  • "Exotic" indexes (function-based indexes, partial indexes, clustered indexes)
  • PRIMARY KEY and UNIQUE constraints

If you're not going to provide similar features, you should think hard about what might happen. If you don't provide something similar to GRANT and REVOKE, somebody will drop a table or delete rows they shouldn't touch sooner or later.

If you are going to provide similar features, you're reinventing the wheel.

6
  • Did you want to write "your users would be better off without a database" - otherwise that sentence does not make sense to me
    – user1822
    Jun 19, 2013 at 22:01
  • 1
    The OP said, "An administrative User can go in and hit 'Create Database' (which is basically a table)..." I meant that they're probably better off with a database that's actually a database than with a database that's really just a table. Jun 19, 2013 at 22:38
  • But "a database that's actually a database" would still need tables to store data?
    – user1822
    Jun 19, 2013 at 22:41
  • 1
    Yes, but it would need more than one table. The OP seems to be saying that the database his code creates is a single table. (But I might have misunderstood him.) Jun 19, 2013 at 22:45
  • 1
    +1 for the reference to Excel, because I definitely foresee phone_1, phone_2, phone_3 in the future for this project. It sounds like the OP is debating between an all-EAV implementation and some kind of build-as-you-go hybrid, neither of which is probably a recipe for success. en.wikipedia.org/wiki/Entity-attribute-value_model Jun 19, 2013 at 23:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.