ADN

Is there a relational database expert in the house? Specifically MS Access 2003. I am building an application using Access and could use some advice on relationships and normalization etc etc.

raum is going to be so happy to see this post in the morning...Dude was born in a database, LoL...

what do you need, em?

I have a light workload today, so make it good.

*sigh* ...

tease.

wow, i did not expect such an eager response!!

I am working on a database to track surgical outcomes/results for laser eye surgery and am having trouble wrapping my noodle around how to split up the tables etc
ie, what variables to put in what table and how to make sure each associated record stays linked to the related information. I could upload a copy of the current db somewhere for you to inspect if you wanted?

-em


*sorry for the delay in replying....I just got done with assisting with surgeries...*

i have uploaded a copy that i just went thru and documented a little so if you want to dl and look at it might make a little sense.

Link to download

Thank you in advance for any insight you may have or advice you are willing to dispense.

i gotta wait till you get on to find the relations and interdependence of the data architecture.

you don't do this much, and probably have a book right next to ya, huh?

see, the first issue is YOU HAVE 9 TABLES with less than twenty rows in them. and countless autonumbers.

if you develop queries off of that, you will have the slowest database you can imagine, and much larger, as well.

there is a way to structure your data to make it bigger, but smaller in size, and much faster,

i suspect that may be what you need to do.

you need to illustrate the data flow to me,..flow charts might be too ask to much, though.

illustrate the flow well, and the proper structure you need will reveal itself.

BTW: what are the chances of me getting a deal on LAZIK in Pennsylvania/New Jersey?

(and yes I am a professional data architecture and system compliance consultant)

b-raum

so is it better to have fewer tables with more fields? i was led to believe more tables with fewer fields per table was the path to normalizing your data. the shell i uploaded is probably like version 4.3 of this stupid thing. I started this project 6+ years ago and have been cobbling together additions and "improvements" ever since. when I started this project I had two tables, one with patient names and birthdates and another with all the pre, intra, and post-operative information. I can work on generating a flow chart, I am sure there is a formal format such things need to be in (I will see if my book has a chapter on that *heh*) what kind of information do you need to be helpful?

The farthest east I go is Toledo Ohio, and the doctors there, while being skilled, are not using the latest available technology and therefore I would not feel comfortable referring you to them. If you are ever willing/able to come to the South Bend area, I could get something going that would give you far better results while also greatly reducing risk of complications. There have been quite a few revolutionary advances in this field over the past 18 months, even if you can not come to this area, I could certainly educate you on what kind of questions to ask and what to look for.

Hi emanon, I'm currently teaching myself about databases with MS Access, so I hope I can help out in some way with your request. But the databases I am building are on a completely different subject... /:D" style="vertical-align:middle" emoid=":D" border="0" alt=":D" /> (PM me if y'all wanna know.....)

Make sure your fields are specific only to that particular table- you don't want duplicate fields in different tables. It's not so much in having more tables with fewer fields, but making sure that those fields are relevant to those tables.

Each table is concerned with a particular theme, eg. Personal Particulars. All pieces of information relevant to that theme are grouped together, and they form the fields of that table.

So for instance, for a table on Personal Particulars, you will have for each field, Name, ID number, Age, eye colour, hair colour etc.

Going back to the theory of using more tables with fewer fields, you wouldn't have a table of the colour of someone's head hair, and in another a table the colour of someone's pubic hair! /:D" style="vertical-align:middle" emoid=":D" border="0" alt=":D" /> That's just getting too anal! And it would clutter up the database and making querying alot more strenuous for the machine.

And of course, don't spread the fields out too thin.

I wouldn't use autonumbers unless I wanted to tag when a particular entry went into the database. So if you are counting when patients specifically receive their surgery in a particular month (or year), then using autonumbers would be useful, as long as each one is entered in order.

Drawing up a flowchart will allow you to see what the database will physically look like, where everything goes etc. You will also be able to interpret how you structure your queries.

I think with your database, the relationships will be "one-to-many". Have ALL the patients listed in one table, with fields for their contact info and particulars. Then you will have tables stemming from that, where each patient will have (these are tables) their surgery history, what surgery they received and the details, etc.



Oh by the way, my father has had laser eye surgery, on both his eyes. His retinas were coming right off the back of his eyes! They had to freeze it and then use the laser to "stitch" the retina back on. He had one eye done, then another. The retina of his right eye didn't stick too well so it had to get re-attached. It worked, but now my father has to wear two different types of glasses.

QUOTE(raum)(and yes I am a professional data architecture and system compliance consultant)


At first, I seriously thought you were a priest..

Then I thought you were an anthropologist, and lecturing at a university.

Then I read you worked in hospitals.

And now you're an IT guy?


Wow, really multi-skilled and varied career! 8)