Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-30-2019, 07:03 AM   #1
bigmac
Newly Registered User
 
Join Date: Oct 2008
Location: wales
Posts: 278
Thanks: 72
Thanked 0 Times in 0 Posts
bigmac is on a distinguished road
Question building a database from scratch

hi all, i have built several small datbases to date with the help of members on this forum.
one of the issues i have is it does not matter how i go about designin the DB i always seem to forget something (password setup ) for example,
do you have a sequence of setting up a DB ? do you decide beforehand what is going to be included in the design, just asking for your input as to how other people go about it and why they use that method

bigmac is offline   Reply With Quote
Old 04-30-2019, 07:09 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,336
Thanks: 158
Thanked 1,699 Times in 1,670 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: building a database from scratch

You need to document all the requirements before even heading off with pen and paper.

Without those and then building a clear list of what's expected out and what's therefore required to go in you are probably going to be building a chocolate teapot.

Think big picture, not minutiae at the opening stages.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 04-30-2019, 08:29 AM   #3
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,948
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: building a database from scratch

To add to Minty's post,
Start off by asking your client "What do you do", "How do you do it", "What do you want replicated in the database", "What additional requirements do you need supported"?

This is a basic "What do I need to build into the database" set of questions. Expect to spend a LOT of time going over what is being done, what needs to happen in the future, what needs to be included for future expansion, and what can be eliminated by doing this electronically.

For myself, getting a list of what reports they will need produced, what is on the reports, how they need to be generated and what they need to report on also helps define additional requirements not always listed originally.

Mark_ is offline   Reply With Quote
Old 04-30-2019, 09:15 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,107
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: building a database from scratch

In the design stages, there are two rules that I apply.

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

Meaning, you must analyze you problem to the point that you can write out a procedure or a data-flow chart or SOMETHING that covers what you are going to do. You need to build yourself a "roadmap" because... When embarking a journey, if you don't have a roadmap, how will you EVER know you have reached your destination.

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first.

Meaning, Access is a "subject matter expert" in building infrastructure. YOU are the expert on content. Access gives you form. You give it substance. If you have a list of desired outputs, a necessary exercise is to assure that every field in your desired output has a valid source within the DB. Sometimes this means back-tracking from outputs to inputs one field at a time. There is also the issue that if you want XYZ then either you need a source of XYZ ... OR you need sources for X, Y, and Z, and the formula that brings them together for that output.

A side effect of having that roadmap I mentioned earlier is that it makes a great start for any after-the-fact documentation you need to build because it will include information on WHY you did something. Or why you didn't.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-09-2019, 03:59 PM   #5
Bullschmidt
Newly Registered User
 
Bullschmidt's Avatar
 
Join Date: May 2019
Location: USA
Posts: 22
Thanks: 0
Thanked 4 Times in 4 Posts
Bullschmidt is on a distinguished road
Re: building a database from scratch

Since I've already created many databases, when starting a new project I almost always try to figure out which one that I've already done that it would be closest in design to and start with a copy of that and then add/edit/delete stuff as needed...
__________________
Freelance Web and Database Developer

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Bullschmidt; 05-11-2019 at 04:56 PM.
Bullschmidt is offline   Reply With Quote
Old 05-09-2019, 04:43 PM   #6
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,688
Thanks: 27
Thanked 510 Times in 483 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: building a database from scratch

When building a DB the only thing of any importance is design of the tables. If the tables are designed correctly you can build code, queries, forms, reports to support user needs. Most people want to get to the "sexy" stuff first the GUI which is irrelevant..
MajP is offline   Reply With Quote
Old 05-09-2019, 07:15 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,107
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: building a database from scratch

So true, MajP. My late father-in-law was a carpentry contractor. He was into doing things the right way in the right order. There is an old carpenter's rule: Measure twice, cut once. Not to dissimilar from our variants: Analyze twice, implement once.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-10-2019, 07:26 AM   #8
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,948
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: building a database from scratch

MajP,

I've found that doing a mockup of the screens helps drive some of the data requirements. You can sit down with a client, work out everything they say they need at the table level and completely miss pieces as they can't "See" what they will be working with. This can be little items, such as "Where's their next due data and amount owed?" to "But we need some way for us to remind all sales people of what promotions we have right now!".

This helps avoid misunderstandings later, the kind of thing that can sour an otherwise good relation with a client.
Mark_ is offline   Reply With Quote
Old 05-10-2019, 07:43 AM   #9
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 853
Thanks: 54
Thanked 40 Times in 34 Posts
MickJav will become famous soon enough
Re: building a database from scratch

I use this https://databasedreams.createaforum....creenshots-20/


To help stucture what I need I must admit it works better when there's a client as I would normlly have a lot of paperwork to help structure the project before It's actually started
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

MickJav is offline   Reply With Quote
Old 05-10-2019, 07:47 AM   #10
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,038
Thanks: 78
Thanked 1,996 Times in 1,944 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: building a database from scratch

bigmac,

Here is a link to various articles in several formats re Database Planning and Design and related info. The "knowledge nuggets" from BA_Experts are humorous as well as informative.

I agree that getting the tables and relationships designed and vetted to support the business is critical. But getting the "client/customer" to identify key outputs and to describe processes is also , but less, important.


UPDATE
I had "but less" originally, but following Doc's comment further down in the thread, I have removed that. User requirements are key to getting the right application. Properly designed and vetted tables and relationships are key to the application right.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 05-10-2019 at 01:58 PM.
jdraw is offline   Reply With Quote
Old 05-10-2019, 11:58 AM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,107
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: building a database from scratch

Actually, jdraw, we would be quibbling about minor differences here, but I think getting the customer's input is crucial to getting the tables properly defined, since only the customer can advise you of true requirements. Which is why I place customer feedback 1st and peer review 2nd. To be honest, nobody EXCEPT the customer can really tell you what you need and that is why we have that old phrase, "Even if he's an idiot, the customer is always right."

Haven't heard the first part that often? Yeah, we usually don't say that around customers. Bad for business.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-10-2019, 12:22 PM   #12
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,948
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: building a database from scratch

LOL @ Doc...

P.S. how often did your late father in law tell you about customers who decided that things were omitted only AFTER they had seen the final product? Something like "But there's supposed to be a glass window in my door so I can see who's outside! Don't you KNOW THIS???" I've been reading http://clientsfromhell.net/ lately.
Mark_ is offline   Reply With Quote
Old 05-10-2019, 01:43 PM   #13
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,038
Thanks: 78
Thanked 1,996 Times in 1,944 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: building a database from scratch

Yes Doc, I agree - you have to get and vet the client(s)'s requirement.
I was just trying to extend MajP' comment
Quote:
the only thing of any importance is design of the tables
.

I have edited that previous comment.

In effect, the tables and relationships have to support the business of the client. And that takes effort and patience; and vetting that design with sample business scenarios often is an eye-opening experience.

We used to work with prototype designs and stub processing to get/confirm/extract "real requirements". A mock up of tables and relationships along with stump the model.
Stubs identifying major steps and instead of detailed logic, we used messages to relay eg What goes here or what to do (verify Contact) just to make sure the order of events aligned with customer needs. Often explicit errors were put into these stubs to get client to show us "how we got it wrong" in order to solicit details. Sometimes a change in processing sequence was experienced by the client and incorporated into interface design.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 05-10-2019 at 01:56 PM.
jdraw is offline   Reply With Quote
Old 07-11-2019, 03:09 PM   #14
adrianscotter
Registered old fart!
 
adrianscotter's Avatar
 
Join Date: Jul 2014
Location: Norwich, UK and Lehigh Acres, FL
Posts: 117
Thanks: 12
Thanked 4 Times in 4 Posts
adrianscotter is on a distinguished road
Re: building a database from scratch

I personally like to spend a couple of days with the client, find out what they want to get out of it, get samples of paperwork and anything they use in the course of their real world work at this moment in time. The usual non disclosure stuff applies... Once I know what they want out of it, I know what they need to put in. From there I can suggest what may or may not be useful in the future, ask relevant questions about what they expect and for suggestions of what else could be useful. I have a core ‘customer’ database that I use. This contains a simple database of name, address, telephone etc., etc. It also contains a user table and login with username / password plus a navigation screen that I can add to. Basically my main building block, it saves me a couple of days on every project. I also like to make my forms look like the paperwork systems they’re used to.
adrianscotter is offline   Reply With Quote
Old 07-11-2019, 03:36 PM   #15
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,948
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: building a database from scratch

Quote:
Originally Posted by adrianscotter View Post
I also like to make my forms look like the paperwork systems they’re used to.
Oddly, this is one piece I often don't do. Rather, I prefer sitting with a few employees to see HOW they fill out the form (order for fields) rather than see what manager expect them to use. It has often been very entertaining to see the first field on a form filled in last (and finding out it isn't actually USED anyplace else) and be told "Its just how we do it".
Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Building a web database, how and what do i need? Guus2005 Access Web 8 12-05-2017 08:49 AM
Building Access DB from Scratch ryancho Tables 2 04-23-2014 01:27 AM
Building a database from scratch ccastell88 General 3 06-28-2007 06:01 AM
I need help building a database homevestors General 4 03-07-2005 03:06 PM
[SOLVED] Help! Developing an HR database from scratch mortiz21 General 4 04-17-2003 06:01 AM




All times are GMT -8. The time now is 08:06 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World