DB design thoughts??

[grawsha2000]

Hi,

I'm in the process of designing a DB (typical management system DB; 2
transaction tables and about 5 look-up ones )for one of the departments
in our company. The user wants this DB and thusly the client (forms,
reports..etc.) solely for his department. However, I do expect soon
after deployment that other users want similar DBs and clients,
therefore, facing problem of integrating such DBs, if company
management wants to implement it as enterprise DB. My question (may be
you could also tell me about other groups specialized in these kind of
issues), how should I create such DB? Should I create extra look-up
table for departments and have each one with its own ID and link it to
the main transaction one

MTIA,
Grawsha

[David Portas]

I'm not sure there's such a thing as a "typical" management system
database - at least not from the point of view of logical design. A
"look-up" is really a UI feature - I suggest you avoid that term when
talking about database design. It may seem at first like a minor detail
of terminology but it it helps to treat all entities as equal in your
logical data model. Identify the entities and attributes of interest
and create a normalized design from there.

You are probably wise to anticipate extending the data's use beyond one
department. In an enterprise environment it usually pays to share data
rather than fragment it. Department is therefore likely to be one of
the entities in your model.

--
David Portas
SQL Server MVP
--

[grawsha2000]

David,

Thanks for the info. Since more than one dept. will end-up using this
DB and they may need to know their dept. no when using this db, I would
like to avoid using Identity field for such thing (numbering records
using identity field).

Any suggestion about other ways of numbering departments?

Grawsha

[David Portas]

If there's a department number that your organization already uses then
use that in the DB.

If you have to come up with your own then one handy approach is to
assign ranges of codes to organizational entities and sub-entities.
Example:

100 Finance
101 Accounts Payable
102 Credit Control
103 Management Accounting
....

200 IT
201 Development
202 Network Support
....

This is useful because you can extract departments and their
sub-departments with a range query (IT: depno>200 AND deptno<300 ).

--
David Portas
SQL Server MVP
--