How to use a naming convention for large databases?

Question

I am busy developing 2 web based systems with MySql databases and the amount of tables/views/stored routines is really becoming a lot and it is more and more challenging to handle the complexity.

Now in programming languages we have namespacing e.g. Java packages, C++ namespaces to partition the software, grouping it together to make things more understandable. Databases on the other hand have more of a flat structure (MySql at least) e.g. tables and stored procedures are on the same level. So one have to be more creative, creating naming conventions, perhaps use more than one database or using tools to visualize things.

What methods do you use to ease the pain? To be effective while developing your databases? To not get lost in a sea of tables and fields and stored procs?

Feel free to mention tools you use also, but try to restrict it to open source and preferably Linux solutions if thats OK.

b.t.w How many tables would a database have to be considered large in terms of design?

Answer 1

The only solution I've found that is generally applicable is to develop a series of prefixes and apply them to tables (for instance, tables relating primarily to Human Resources would all start hr_). I generally carry the prefixes across to other "objects" in the application (forms, reports, views, stored procedures).

This solution is far from perfect and is something of a hack but it does bring a modicum of order to the system.

Answer 2

Oracle E-Business Suite has over 25,000 tables and around 33,000 views. I would say that was a large schema.