Help! Database Designers... got a 'best design' question for ya.

[Guest]

Here's what I have...

I have several departments whose critical processes depend on other
departments. For example, in order for IT to carry out their critical
process of ordering parts, they rely on the supply chain & procurement
departments (and perhaps several others).

I need to design a database from which i can pull reports to show each
department and the departments they depend on for each process (i.e. IT
depends on Supply Chain/procurement/whatever else) and also reports saying
what all departments and processes depend on Supply chain (i.e. Supply chain
needs to support/serve IT and likely others on these specific processes)

Does that make sense? What's the best design for something like that??

Currently, i have a table with one record for each process that includes a
process ID (autonumber, primary key), the department using that process (in
my example, IT), a description of the process (like "order parts/supplies"),
and then each of the other departments on which ALL depts could rely (each of
these is a yes/no field)... so each field has its dept, process, and all
other depts on which it relies. The problem is that there are 80+ types of
depts on which others depend...

Is this clear as mud?

Eventually, i'll also need to identify whether the departments that are
depended on are internal/external/interdepartmental...

so what's best for this???

 

[aaron.kempf]

I would reccomend using Access Data Projects and keeping everything on
the same server

maintenance is much easier for cross-department databases when you keep
everything on a single server.

you should just try to keep it in a single database; but connecting to
multiple databases on the same server is easy

-Aaron

 

[Guest]

I see you needing three to four tables.
Document --
DocID - Autonumber - primary key
DocNUM - text - per your configuration management numbering scheme
Title - text
OPR - number - integer - foreign key
Revision - text - unless you use the fourth table DocHistory.
RevDate - DateTime - unless you use the fourth table DocHistory.

Organization --
OrgID - Autonumber - primary key
Department - text
Name - text

DocOrg --
DocOrgID - Autonumber - primary key
DocID - number - integer - foriegn key
OrgID - number - integer - foriegn key
User - Yes/No - Yes = user and No = interface.
Inactive - Yes/No

DocHistory --
HistID - Autonumber - primary key
DocID - number - integer - foriegn key
Revision - text
RevDate - DateTime

Setup one-to-many relationship between the primary key and the corresponding
foreign keys.