 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / December 2005general db design question
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 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. > context: > vb6 / ado / adox 2.8/ mdb file format / jet 4.0 provider (not using [quoted text clipped - 58 lines] > 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. > > 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/quickref/ado_intro.html http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdms cadocodeexamplesinvisualbasic.asp http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdms cadoprogrammersguide.asp http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/adms cadoddlexamples.asp http://www.vbexplorer.com/VBExplorer/wrox/sample1061.asp http://www.vbexplorer.com/VBExplorer/vb_feature/june2000/Database_Beginner_ADO_D AO.asp http://www.vbexplorer.com/VBExplorer/library/simpledb.asp http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/dasd kadooverview.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/quickref/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/scripts/ShowCode.asp?txtCodeId=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/default.asp?url=/library/en-us/dndao/html/daot oado.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. :-) 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. > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.