How do you make life easier for yourself when developing a really large database

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

Definitely, definitely use naming conventions. MySQL database design is one of the last places that I use hungarian notation, but I start all my tables with "tbl", all my views with "v", etc.

Additionally, I build multiple database diagrams in MySQL Workbench, generally at least one diagram per domain aggregate, that help me visualize the "modules" in the architecture.

This is one area where a product like Sql Server has big advantages, as database objects can belong to multiple schemas inside a database, much like namespaces in programming.

Answer 2

Well... there is no real solution in MySQL. With some databases (PostgreSQL for example) you do have namespaces and you can do this.

You could ofcourse work around it by using multiple databases to emulate namespaces but that might give a lot of problems.

Personally I would simply name everything in such a way that your management tool can distinguish it (like phpMyAdmin automatically uses underscores to group databases).

Answer 3

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 4

In some databases you have schemas you can use, but I think not in mySQL. Naming conventions to keep related tables together are your best bet. One thing I try especially to do is be very consistent in naming fields in different tables exactly the same thing. If my user table calls for a user_id, then I don't want to see it as person_id, userid, User, etc in different related tables. Also when using the same type of field from table to table use the same datatype (and size if it is string data). Then you won't be continually having to convert data to do joins.

As far as how many tables you need to be a large database, that is more a function of how many records in the tables not the number of tables. Many small databases have hundreds of tables. I would almost never worry about the number of tables unless I saw someone creating tables something like Financials2009, Financials2010, etc.

Answer 5

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

Answer 6

A solution that worked well for me on one project was that we divided the database into chunks, then we drew a big ERD (we used Corel, actually, though there are many fancier tools available), we color-coded the boxes for each table to show what chunk each was in, then we printed it out on a large-format printer so it was like 5 feet tall and 10 feet wide, and we hung it on the wall of my assistant's office. Not a high-tech solution, but it was incredibly practical.

We were also meticulous about consistent naming, to echo HLGEM's answer.

In retrospect, a naming convention that would have prefixed each table name with a "chunk name" would probably have been a good idea, but we fared quite well without it.

As to how big is big? I don't know, a highly subjective question. I generally think that a database is big when I can't picture the whole thing in my head at one time. In practical terms, I guess when you pass a few dozen tables. Number of records is pretty much irrelevant: A database with two tables that have a billion records each would be easy to comprehend; a database with a 1000 tables that have ten records each would be hard to comprehend.

Answer 7

Databases on the other hand have more of a flat structure (MySql at least)

...but you can have multiple databases running on the same mysql instance, e.g.

SELECT *
FROM common.address adr
purchasing.orders pod
WHERE pod.cust_id=adr.cust_id

(NB try to avoid the mysql 'USE dbname'). It's a good idea to standardize your alias names in queries.

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

I don't think that there's a standard metric. I'd probably start getting confused around 50. One of the Oracle DBs I look after has 1567 (yes, it is normalized (sort of))