general db design question

[MP]

context:
vb6 / ado / adox 2.8/ mdb file format / jet 4.0 provider (not using Access)

trying to design first database
I work for a very small company, detailing building 'components'

There is currently no database usage in our company.
On my own time I'm trying to learn database/ado so as to improve the way we
work.

My first database idea is to have two tables, tblJob and tblComponent
Each job is a building, each building contains Components, each Component
has information I need to track/collate/report on etc.

Currently all files related to a specific job are stored in a folder on
server by jobnumber
server / 2005 / 05001
server / 2005 / 05002
server / 2005 / 05003
etc

initially I envisioned a database for each jobnumber
so in 05001 there would be a file 05001.mdb and in 05002 there would be
05002.mdb
then for the 05001 job I can query the 05001 table and ask about components
on that job etc.

at this point that would be workable with my very small initial design
criteria ideas which are swimming around in my head trying to become
realized....

However, I can also see a larger world view where there would be one .mdb
for the company and each job would become an entry in a tableAllJobs in the
companywide.mdb file....

that would seem to make it easier to ask company wide questions, like how
many components existed in 2005, versus only being able to ask how many
components in job 05001 for example.

however at this point we don't ask any questions like that...

my thought was that as we got more sophisitcated? <g> in the future we might
want to ask broader questions of our data.

thinking that it's easier to set up the database right the first time rather
than add to it in the future as needs evolve...???

is there any reason to set up a company wide mdb rather than job specific
mdbs to allow for theoretical possible future queries even though I have no
immediate need for those kinds of queries at the present time?

any thoughts on which would be a better approach?

tia
Mark

[Geoff G]

The single database for the company containing all the jobs is the
right way to go - after all the company has a one to many relationship
with the jobs that it does. It also has the same relationship with the
components it stocks and with the people it employs.

Personal experience with users is that even if you cannot think of a
reason for a sophisticated query at the stage you describe, someone
will ask for it eventually (usually your boss five minutes before a
crucial budget meeting).

The more time you spend on the design process before you even start the
job the better. The trouble is that without good guidance to begin with
you can tie yourself in some horrible knots. So.. start reading about
relational databse design.

One of the best descriptions of how to do this that I've found is in
Chapeer 1 of 'Beginning Access VBA' by Denise Gosnell. You don't have
to plan to program in VBA for this to be a good intro to the basics.

Good luck with the project.

[Dmitriy Antonov]

"MP" <no****@Thanks.com> wrote in message
news:in****************@tornado.rdc-kc.rr.com...

context:
vb6 / ado / adox 2.8/ mdb file format / jet 4.0 provider (not using
Access)

trying to design first database
I work for a very small company, detailing building 'components'

There is currently no database usage in our company.
On my own time I'm trying to learn database/ado so as to improve the way
we
work.

My first database idea is to have two tables, tblJob and tblComponent
Each job is a building, each building contains Components, each Component
has information I need to track/collate/report on etc.

Currently all files related to a specific job are stored in a folder on
server by jobnumber
server / 2005 / 05001
server / 2005 / 05002
server / 2005 / 05003
etc

initially I envisioned a database for each jobnumber
so in 05001 there would be a file 05001.mdb and in 05002 there would be
05002.mdb
then for the 05001 job I can query the 05001 table and ask about
components
on that job etc.

at this point that would be workable with my very small initial design
criteria ideas which are swimming around in my head trying to become
realized....

However, I can also see a larger world view where there would be one .mdb
for the company and each job would become an entry in a tableAllJobs in
the
companywide.mdb file....

that would seem to make it easier to ask company wide questions, like how
many components existed in 2005, versus only being able to ask how many
components in job 05001 for example.

however at this point we don't ask any questions like that...

my thought was that as we got more sophisitcated? <g> in the future we
might
want to ask broader questions of our data.

thinking that it's easier to set up the database right the first time
rather
than add to it in the future as needs evolve...???

is there any reason to set up a company wide mdb rather than job specific
mdbs to allow for theoretical possible future queries even though I have
no
immediate need for those kinds of queries at the present time?

any thoughts on which would be a better approach?

tia
Mark

I agree with Geoff. I don't think there is any single reason of having
multiple DBs, even at the beginning of development. In reality, it just
complicate development, rather than simplify it.
Some reasons of having one DB:
1. Less space required (depending on number of jobs the difference can be
huge)
2. It is more logical and thus more maintainable
3. It is more flexible (as you mention it will you more ways of using data,
which otherwise would be much more complex)
4. Combining multiple DBs into one in the future may become a project by
itself.
5. Development is easier (at least, after you get some general idea on DB
design and SQL language)
6. You have to control, maintain and troubleshoot much less number of
objects, which pays for itself over time.. You have less trouble in case you
need to relocate entire system.
7. Depending on your design, it may take much less resources during
execution (memory, bandwidth).
8. And so on.

And, as I said, I don't have any reason to support multiple DB, unless there
are some additional and unsaid requirements.

Starting with inappropriate concept may lead to expensive problems - spend
some time on studying, as suggested, before starting the project.

Dmitriy.

[MP]

"Dmitriy Antonov" <an*********@netzero.net_NOT_FOR_SPAM> wrote in message
news:e6**************@TK2MSFTNGP15.phx.gbl...

"MP" <no****@Thanks.com> wrote in message
news:in****************@tornado.rdc-kc.rr.com...

context:
vb6 / ado / adox 2.8/ mdb file format / jet 4.0 provider (not using
Access)

Starting with inappropriate concept may lead to expensive problems - spend
some time on studying, as suggested, before starting the project.

Dmitriy.

Thank you both.
I will go with your mutual suggestion.
I have been spending as much time as I can on studying and this is part of
that process.
(Dmitriy may recall answering several of my previous questions over the past
month)

At some point soon, I need to stop studying and start doing!
:-)

fwiw some of the resources I've been consuming:

Fundamental Microsoft Jet SQL for Access 2000
Acey James Bunch

Data Modeling As Part of Database Design

Universal Data Access Using ADO

FundamentalsOfRelationalDatabaseDesign

Database Normalization Tips
by Luke Chung

http://www.w3schools.com/ado/default.asp
http://www.able-consulting.com
http://www.bkent.net
http://www.devguru.com/Technologies/...ado_intro.html
http://msdn.microsoft.com/library/de...isualbasic.asp
http://msdn.microsoft.com/library/de...mmersguide.asp
http://msdn.microsoft.com/library/de...dlexamples.asp
http://www.vbexplorer.com/VBExplorer...sample1061.asp
http://www.vbexplorer.com/VBExplorer...er_ADO_DAO.asp
http://www.vbexplorer.com/VBExplorer...y/simpledb.asp
http://msdn.microsoft.com/library/de...dooverview.asp
a programmer's guide and reference.
http://makeashorterlink.com/?S33A25952
http://www.able-consulting.com/ADO_Conn.htm
http://www.devguru.com/Technologies/...ado_intro.html
http://www.able-consulting.com/ADO_Faq.htm
http://www.able-consulting.com/tech.htm
ADO Tutorial For Absolute Beginners
http://www.planet-source-code.com/vb...12050&lngWId=1
ADO Tutorial:
http://www.xtremevbtalk.com/showthread.php?t=66994
ADO Reference Guide:
http://www.devguru.com/technologies/ado/home.asp
Migrating from DAO to ADO (good document for ADO code snippets):
http://msdn.microsoft.com/library/de...l/daotoado.asp
ADO Connection Strings (Carl Prothman):
http://www.carlprothman.net/Default.aspx?tabid=81
Connection Strings:
http://www.connectionstrings.com

and several hundred related posts on the various ngs.
:-)

[pietlinden]

How about loooking through Database Design for Mere Mortals, by Michael
Hernandez. Plain English, and not tied to any particular database.
But a good grounding in what makes for good databases and what causes
bad ones.

[MP]

<pi********@hotmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

How about loooking through Database Design for Mere Mortals, by Michael
Hernandez. Plain English, and not tied to any particular database.
But a good grounding in what makes for good databases and what causes
bad ones.

Thanks for the ref, I'll look for it.
Mark