Documenting a collection of databases and their relationships with each other

database-designdocumentation

As part of a project I'm currently involved with we need to identify where various data points are held within existing systems, with a view to integrating them into our central integration engine. We're part of an NHS Trust, so there are many databases, reporting databases, etc.

There seem to be many tools to design & document a single database, but what I'm after is something a level higher, which can record the relationships between data elements in different databases. I can't find anything on Google, but I wonder whether I'm just using the wrong terms.

At the moment we're solving the problem by recording the database, table, attribute & a sample query for each data item. A system which could at least group the different data elements by table, database, etc. would be useful.

It seems this would be useful to any organisation with many disparate databases, so want to check it doesn't already exist (or that there's not a better solution for the problem).

Best Answer

Firstly, let me say from the outset that you have my deepest sympathies! :-) Maintaining a true-to-life up-to-date schema of even a single database is bad enough, but having to maintain a single point of truth (SPOT) for a multi-database disparate data-source environment is going to be nothing short of hell on earth!

You _must_ establish a SPOT for your systems - one path in, one path out. If you don't have serious senior management buy-in for this project, it is doomed from the start!

Take a look here and follow the links to K. Scott Allen's posts. Check out Agiledata.org and Allen's site ambysoft.com. Establish the relationships - there are schema generation tools for every database under the sun, but given that I imagine you have a large hetergeneous environment, you probably have everything from Oracle Enterprise servers down to spreadsheets on individual's machines.

Bear in mind that there's only one thing worse than no documentation and that's inaccurate documentation (think prominent, but defective, fire extinguisher). Furthermore, if your central system is seen to sub-optimal, all that will happen is that workers on the ground it will be bypassed and become a shelf-ware irrelevancy.

The approach I would take in this situation is to establish a framework whereby there is [1 person | a small group] responsible for a given system and it is up to them to maintain their part of the schema documentation and ensure that they have the tools to do so. Yours should be more of an organisational/oversight role. Make it SOP that your group be informed of any schema changes (immediately) so that their (and by extension the central) meta-data repository is kept up to date. IMHO, you face an uphill battle and I wish you the best.

As for tools for documenting a given system, leave that up to the experts in those systems. As for a tool which consolidates "meta-meta"-data, I'm not aware one - apart from graft that is.

Related Solutions

MySQL Database Design – One Big Database vs Several Smaller Ones

I ran a system with the best part of a thousand databases, spread across multiple servers. They were all an identical structure and were synchronised with a template database which was on each of the machines.

This allowed me the ability to migrate databases from one db to another if one was getting excessively over-loaded, and as the client mix changed, I could create new databases on different servers to load balance across the servers. This was the biggest advantage I got from the system, in that I had multiple large lumps of tin performing multiple complicated queries simultaneously on the separate servers.

The great thing about this, is that you can add servers to the configuration at your own speed, as each server starts to get over-loaded, add another into the mix, migrate some dbs across to the new server and end up with a nicely load balanced set of servers. A really nice and simple way to add scale to the system as and when it is required!

The reason I went with this approach rather than the single huge database approach, was the sheer size of the potential database that would have been created... each of the 1000 databases had 200 tables, and many of the individual tables within each of the databases comprised many hundreds of millions of rows of data!

A single database configuration would have required certain tables (approx 8 of them) to have multi-billions of rows of data, and the total db size would have been over 10Tb. We were able to have multiple servers with 5Tb of RAID 10 storage, with many databases on each.

That's what I would do! Hope it helps your decision making... :)

Can relationships have relationships

You want to avoid any inconsistency in prices - and you have already identified this in your question.

I'd say use a "Pricing" table which stores Supplier-Parts(PK) and Prices(Float or Decimal value etc).

That way, each part from a different supplier has a different price. In other words, the price depends on what the Part is AND who the Supplier is (Supplier-Part is the Primary Key or Unique Index).

The Purchases table should just include records from this new "Pricing" table - which links to all the info from Parts, Suppliers and Prices.

Makes sense?

EDIT: I just noticed. You are relating Parts and Suppliers again in your Purchases table, when you already do it in your Sells table.

Just have these 4 tables:

Parts: PartID (PK), PartName
Suppliers: SupplierID (PK), SupplierName
Pricing: (PartID,SupplierID) (Unique Index of these two Foreign Keys), Price
Purchases: PurchaseID (PK), (PartID,SupplierID) (Foreign Key), Quantity

Best Answer

Related Solutions

MySQL Database Design – One Big Database vs Several Smaller Ones

Can relationships have relationships

Related Question