Design decisions (global or fragmented data files ?)

[Thomas An]

Hi all,

It is often the design decisions that I find most difficult in a database.

Here I have a general (hypothetical) question:

- Would you suggest a company database to be global (monolithinc) with all data in a central location and provide a UI that gives selective access to the data?

For example, suppose you are designing a database for a large parent corporation that acts as an ubmrella for several smaller companies (including franchises, subsidiaries ... etc)the child companies might not be offering related services (for example one might a bicycle company and another might be an exercise equipment company).

Should the database be built so that all data are stored in a gigantic central file and then provide user interfaces to all "children" companies to selectively access their portions of the data ?

... or should the data be split into a zillion smithereens independent and unique to each child company ?

[Ender]

This is an interesting question, but one that I'd guess will remain hypothetical. I don't think FileMaker is often used by large companies to manage all the data for their subsidiaries. For one, the limitation of 250 simultaneous FileMaker client connections makes this unlikely. But I think there are some other practical and business logic issues that would steer the design to use separate files and probably separate servers for each business unit.

These limitations/considerations might include:

1. Physical locations of the companies. It may be best to keep the server at the physical location of each business unit's administrative headquarters so that those employees can get local network speeds when running reports, imports, and exports.

2. Number of client connections. As I said, there is a 250 FileMaker Pro client limitation for each Server.

3. User management. It may make the most sense to let someone in each business unit manage the user accounts for thier systems. I don't imagine there would be many accounts that need access to systems of all of the business units, so in my opinion, local control would be better.

4. Data interaction vs. independance. If the businesses are truely different, then there probably isn't too much data that they would need to share. In fact, the systems that each business uses would probably look very different (and have different structures.) Some might think it would be nice to have a shared Customer database, but I'm not so sure. While some customers could be the same for the different businesses, most would be different.

If you were talking about multiple branches (or franchises) of the same company, then my argument would be for a centralized system for all of those branches because they would all share the same structural needs and there would be advantages to sharing the same Customer data. A Wells Fargo customer expects to be able to get their money from any branch they visit, rather than just the one they opened the account in.

[Thomas An]

Hello Ender,

Thank you !

The points you brought up make excellent sense ! (needed the reassurance and help putting things in perspective)

Another concern with centralized model is the added complexity of having to perform data separation when a business unit is sold to another organization.

The question was hypothetical, because I am struggling on the idealistic level "how far should we go refining the database architecture trying to devise a most generic and least limiting solution"

Indeed it is this kind of considerations which make me run out of "oxygen", while trying to figure out a most broad, elegant, and minimalist design. (maybe I am dwelling too much on the design phase running the danger of becoming an architecture astronaut... just finnished reading this article from another thread)

Cheers,

Thomas

Edited July 26, 2006 by Guest

[Ender]

Yes, while it's useful at the start of a project to brainstorm about all the things you might want the solution to do and all the features you might want the solution to have, it's important to think about the practical limitations and the difficulty of implementing those features.

When I get a feature request that would be rather tricky to implement, I tell them "Yes, it's possible, but it's a lot of work." I also try to consider what the consequences are of different structural choices. Once I understand the choices and can estimate project times, I can give the client the options.

[bruceR]

An alternative is to learn a bit about extending your solution with UDDM - User Defined Data Model - such as by looking at Ginko here: http://www.jonathanstark.com/downloads.php

Edited September 5, 2006 by Guest