Database design in theory

[srlake314]

Hello all.

I have "some" background in programming, however, not in VBA nor SQL. My database design is not anymore complicated than any other and as I remember in my programming of the past, take each step, baby steps and build upon those baby steps until you have a fully working program.

So with that in mind. I based my database on forms first and a few tables of sample information to get things started and eventually, properly linked.

I have been tracking information in an Excel spreadsheet, not of my design, although I have modified it quite a bit to work in my favor.

To start the design, I walked through my daily process, and even made a word doc with all the steps, no matter how trivial. From there, I created forms that would take place of how I would normally enter into the spreadsheet or write on the printed off "AUTHS" with the patient's info.

Without getting into too much detail of my daily process, I created the forms based on entering said information in a clean layout.

After creating each of the forms, I made search form windows that would pop up whenever I clicked on "search".

What I need to be able to do is:



When I first bring up my "scheduling" form:

• I click on search button. Main search form opens with the option to just enter auth number or detailed search window.
• If I click on detailed button, another search window opens with even more criteria(date of birth, date of appt, auth number, etc.)

The idea is, once I enter the info, a subform in the main search window opens with a listing(one entry or multiple based on the criteria) that I then can click on. This information is then "fed" or autopopulated into my "appt" form. From there I can tell, one if they already have an appointment, or I can proceed to enter the required info into the fields and hit save button to save appointment. All that info is linked to a table, the master table,(the old excel spreadsheet I'm currently working on).

How can I first, get the search windows to "search" the master_tbl(excel spreadsheet) to then fill out the "subform" in the main search window(the big white box)?
Then when I double click on any of the entries, it populates all the fields that have information from the spreadsheet, into my simplescheduler_frm.
That's the basic part of it .

Any help would be appreciated.

I have gone through so many websites, but nothing specifically answers what I'm looking for and so I have a messy vba right now.:banghead:

 

[CJ_London]

you've kind of started from the wrong end - you should really look at table structure first and forms second, however standard practice is a form has only one table and related tables are in subforms so may not be a problem.

It is not clear from your description where your data is - in tables or in a spreadsheet? Also be aware if not already that you cannot edit an excel spreadsheet from access - only view its contents.

One of the main differences about data in Excel and Access is that Excel presents 'short and wide' whilst Access is 'tall and narrow' with the objective of only storing data once - so a number of tables.

To answer your questions in anything more than vague terms, we really need to see your table structure and relationships. I w3ould also suggest that rather than having separate forms, have one form with subforms - it is generally more efficient from the users perspective and (in my opinion) easier to code

How can I first, get the search windows to "search" the master_tbl(excel spreadsheet) to then fill out the "subform" in the main search window(the big white box)?

1. Your form or subform would have a recordsource of the columns you want to see. You would then, on the click of a button or after update event set the subform filter to limit the rows seen based on your criteria - or for a separate form you would open it with the appropriate criteria

Then when I double click on any of the entries, it populates all the fields that have information from the spreadsheet, into my simplescheduler_frm.

you would have some code in a doubleclick event, probably on one of the controls or perhaps the form itself to open it with the uniqueID as the criteria

 

[srlake314]

Well thank you for the immediate response.

I would disagree with creating the forms, because that helps with the look of the gui and reverse engineer or connect the dots so to speak. The reason I created seperate forms is I know that I can have them be "called"in as a subform in a larger form window, so that's not a problem.

As far as the data, yes it's inside a Master_tbl. The original source was an excell spreadsheet, so the master_tbl is all the information that is gathered from the data house and from there other fields(the green labeled) are entered as we schedule, and followup with schedule.

The tables have all the various "data" such as the names of the doctors(which is also a field in the master_tbl, same as all the other important fields. SO not ALL the fields in the Master_tbl are being used for scheduling, but they are relevant for other "features" that later will be reported on by the admin.

The database isn't just for scheduling, but that is the part that I'm more concerned with right now.

I've inserted the spreadsheet fields to give a better idea of the data collected. The dark blue are fields that we as the user do not touch, whilst the green is for our entering.

I don't have any "relationships" built yet between tables because I was unsure how to do that without programming.

 

[srlake314]

OH the unique id or key is the CASENO or Auth.

 

[Frothingslosh]

Do you start building a car by building the outer shell first and then filling in the frame?
Do you start constructing a house by placing the outer walls first and then digging out the foundation?

No, you build from the foundation outward. With a database, the tables and data layout are the foundation. If you build your database based on what you want your forms to show, rather than what you want the database to store, you're going to cause yourself no end of trouble trying to jury-rig solutions that wouldn't be necessary if you had built your database correctly.

There's nothing wrong with knowing up front how you want the UI to look, but don't let it determine the structure of the database, or you'll pay for it in headaches and lots of unnecessary effort later.

 

[srlake314]

Perhaps I should have expanded on the fact that I did create tables as well, but I also had to get the idea across what it was we were looking for in a UI.

It wasn't as if I had completely done the forms backwards and actually, yes, cars are designed backwards LOL. The outer shell is created for form, the basic guts are already understood by the artists, of the shape, but this is totally different.

So, yes I did create tables, but only the basics, as far as their relationships, I wasn't sure how all that would be "hooked" up in Access. The importance of how the forms looked determined the work flow as closely related to how things are done now. They needed to be created to convey how the users could all, well, use it.

Thanks for any useful information given.

 

[jdraw]

I recognize that you have familiarity with spreadsheet and programming. But spreadsheet and database are built on different object models, so knowing one may not help when learning the other. Quite often with Excel and Access, knowing and having experience with Excel, can hinder learning Access/database.
User interface is NOT the same as tables and relationships or structure. I'm not saying UI isn't important, it is just a different aspect of the application.

Relationships are based on your business rules. They are not arbitrary.
One Customer may have 1 or many Orders...


Here is one approach re database design, but there are many.
http://www.databaseanswers.org/approach2db_design.htm

Here is a tutorial that starts with a description of the business and business rules and leads to a data base structure that supports the business. You have to work through it. It does have a solution and series of steps. It takes about 30-45 minutes, but is well worth the effort.

Good luck with your project.

 

[srlake314]

Crap, I had written you a long thank you for your help, but since I have to retype it, I'll just say thank you.

 

[gemma-the-husky]

although you will have an idea of the forms you want, the right place to start is the data. Think about every little piece of data you require. Analyse these into appropriate related tables. By all means use the forms you envisage to remind you of the data requirements - but as others have said, the data is the key.

once you have the right data structure, you will find that forms and reports, and indeed the entire user interface will develop harmoniously. There is much serendipity too, as you (and your users) find new ways to use your data that you probably never envisaged originally. Generally, it's all because you got the data structure right.

Actually, thinking that the form is the "right/only" way to do it, is probably the wrong concept. Printed/Written forms are a solution for a physical system. A dbs let's you build much more sophisticated interfaces.

 

[srlake314]

Very true, I'm not refuting that data is important, not at all. I can think in many levels. I "see" the database and all the data, all the ins and outs, what I don't know as much is, how to use Access or VBA.

My brain thinks in flow charts, faster than I can write down. Creating the data isn't the problem it's learning how to use the tools of Access.

Thanks again everyone who responded with links, and how tos. After all that's what we are all on here to do, learn.

 

[Solo712]

Hello all.

I have "some" background in programming, however, not in VBA nor SQL. My database design is not anymore complicated than any other and as I remember in my programming of the past, take each step, baby steps and build upon those baby steps until you have a fully working program.

So with that in mind. I based my database on forms first and a few tables of sample information to get things started and eventually, properly linked.

I have been tracking information in an Excel spreadsheet, not of my design, although I have modified it quite a bit to work in my favor.

To start the design, I walked through my daily process, and even made a word doc with all the steps, no matter how trivial. From there, I created forms that would take place of how I would normally enter into the spreadsheet or write on the printed off "AUTHS" with the patient's info.

Without getting into too much detail of my daily process, I created the forms based on entering said information in a clean layout.

After creating each of the forms, I made search form windows that would pop up whenever I clicked on "search".

What I need to be able to do is:



When I first bring up my "scheduling" form:

• I click on search button. Main search form opens with the option to just enter auth number or detailed search window.
• If I click on detailed button, another search window opens with even more criteria(date of birth, date of appt, auth number, etc.)

The idea is, once I enter the info, a subform in the main search window opens with a listing(one entry or multiple based on the criteria) that I then can click on. This information is then "fed" or autopopulated into my "appt" form. From there I can tell, one if they already have an appointment, or I can proceed to enter the required info into the fields and hit save button to save appointment. All that info is linked to a table, the master table,(the old excel spreadsheet I'm currently working on).

How can I first, get the search windows to "search" the master_tbl(excel spreadsheet) to then fill out the "subform" in the main search window(the big white box)?
Then when I double click on any of the entries, it populates all the fields that have information from the spreadsheet, into my simplescheduler_frm.
That's the basic part of it .

Any help would be appreciated.

I have gone through so many websites, but nothing specifically answers what I'm looking for and so I have a messy vba right now.:banghead:

srlake,
let me the devil's advocate here. There seems to be an uncomfortable level of consensus here that a database design will always have to start with the design of tables. I don't think that is always the case, and as a matter of fact, I find your basic approach eminently sound - trying to visualize the app functionality and make it conform to the needs of the business. The fact that you verbalized the process before starting to design tables warms the cockles. You know what you are doing. You build an application to fit a business process, not the other way round. Your design should be dictated first and foremost by business rules, controls and practices, not by formal requirements in organizing data. I have butted heads here on this one a number of times before.

Your idea of trying to find a visual model of presenting and search for info that already exists, again is a sound way way of approaching the project. Definitely it is not "starting from the wrong end". Often it is the user interface that matters the most.

I guess you are getting stuck on the next step which does involve the organization of data. In order that you present the data you need to know where to fetch and how it interacts and that touches on the table design. You could retrieve and edit (!!!) Excel data in Access but you do not want to do that. So you need to think through where your "master table" sits, whether it is in fact one or more tables and if it is the latter, how the tables hook up. To do that, you need to understand the relational model. That will take some time to digest, but you need to negotiate that learning curve. So, to sum up my view of the conundrum you are in, I would say, your excellent top-down approach breaks down when you are trying to work with a detailed design which you have not thought through and for which, it seems, you have not yet developed your own skillset. Maybe I am stating the obvious.

Best of luck!

Jiri

 

[srlake314]

Dyslexics of the world unite! Idneed .

Thanks for that vote of confidence. Yes, I will be working with "database designers" who are the programming geniuses, and that was all the more reason I wanted to create the forms. Especially knowing the mindset of those I'm going to be working with, I knew if I didn't communicate the whole process of what we do in our job, that the forms created from the raw data, would be, less than friendly; I've seen their other forms and they are horrible. Most programmers, in my experience have little to know design experience and thus their forms are very unfriendly. I've seen it at very, very large healthcare companies.
I'm a designer, first and foremost(no need to go into my whole life LOL), I know how to make things user friendly, it's been my talent to be able to walk in both worlds, a moderator, translator so to speak.
What I never did was, learn Access. I had tinkered with it way back in the early 90's or so, but only to make a video tape, yes video tape, library tracker .
So yes, I will really bust out the relational aspect very, very soon. And now, the "programmers" understand what I want from this database and not just the data.

Thanks again!

 

[srlake314]

Solo, btw, you should see the pages and pages of notes I wrote BEFORE even creating the forms and the basic tables .

 

[gemma-the-husky]

Dyslexics of the world unite! Idneed .

Thanks for that vote of confidence. Yes, I will be working with "database designers" who are the programming geniuses, and that was all the more reason I wanted to create the forms. Especially knowing the mindset of those I'm going to be working with, I knew if I didn't communicate the whole process of what we do in our job, that the forms created from the raw data, would be, less than friendly; I've seen their other forms and they are horrible. Most programmers, in my experience have little to know design experience and thus their forms are very unfriendly. I've seen it at very, very large healthcare companies.
I'm a designer, first and foremost(no need to go into my whole life LOL), I know how to make things user friendly, it's been my talent to be able to walk in both worlds, a moderator, translator so to speak.
What I never did was, learn Access. I had tinkered with it way back in the early 90's or so, but only to make a video tape, yes video tape, library tracker .
So yes, I will really bust out the relational aspect very, very soon. And now, the "programmers" understand what I want from this database and not just the data.

Thanks again!

Repeating ourselves a bit - but that's the whole point.

The user interface is nothing to do with the date model. A bit of a tautology, but the data model needs to be the most efficient design to store the data you are modelling.

A harmonious user interface is really nothing to do with the data model per se.