Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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.

share|improve this answer
    
Did you want to write "your users would be better off without a database" - otherwise that sentence does not make sense to me –  a_horse_with_no_name Jun 19 '13 at 22:01
    
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. –  Mike Sherrill 'Cat Recall' Jun 19 '13 at 22:38
    
But "a database that's actually a database" would still need tables to store data? –  a_horse_with_no_name Jun 19 '13 at 22:41
    
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.) –  Mike Sherrill 'Cat Recall' Jun 19 '13 at 22:45
    
+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 –  Michael - sqlbot Jun 19 '13 at 23:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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