Basic Database Design

Tina thanks for your reply.
Well as you already notice, I’m “extremely� green on the Access, and I’ve
nothing to do with programming or Informatics.
But I like to work with Access and already did some Databases, of course for
my personal use only. I realize now that I’m in my “prehistoric age� of
Access knowledge.
OK. Now I have the right structure for my Database in terms of Tables and
relations between them, but my problem now is with the Form.
Imagine I’m at work, the Patient appears and I open my Database on the
FormPatient
where I have the fields:

Name: _______(I ask to patient her name and I type it here - txtfield______
Age: (or DOB as you suggest) : ___I ask the patient and insert here the date)
__
Date: ____( date of the visit)____
Insurance Company: ___I ask the patient: and I select from a combo Box
cboCompany– Rowsource will be tblInsCompanies __\
Category: __(here I’ll select the category of service that I will do to that
patient)__
this is because there are a lot of services ( a hundred or more) and I want
to filter the next combobox in order to diminish the list of Services __
cboCategory – rowsource tblCategory
Service: ____(here I select the service already filtered on the previous
cboCategory that I’m doing now to that patient – and it will be selected
from a combo box cboService – rowsource tblServices .
UnitPrice: ______
The next step is that the field UnitPrice will be automatically filled with
the price that the Insurance Company I select will pay for that particular
Service that I did.
I don’t know if what I wrote is understandable or makes any sense.
so if you can help with this I’ll be thankful
Silvex


tina wrote:
I have a list of Twelve Insurance Companies
Each Insurance Company furnishes all the products, but with different prices
from one
Insurance Company to another.

okay, having read your further explanation as well, we're actually talking
about services paid for by insurance companies.
so one service may be paid for by many companies, and one company may pay
for many services. also, one patient may receive many services (with payment
from one or many companies), and one service (from any company) may be
received by many patients. these are many-to-many relationships, which are
not correctly represented in your posted table design. suggest the
following, as

tblCategories
CatID (primary key)
CatName
CatDescription

tblServices
ServID (pk)
ServName
CatID (foreign key from tblCategories)

tblInsCompanies
CoID (pk)
CoName
CoPhone
CoFax
i'll go with that setup, though it's not normalized; proper design would
call for a communications table (linked to tblInsCompanies) where you can
enter multiple records for each company - phone, fax, email, website, etc.
or at the very least, a simple phone number table, linked as noted above.

tblCompanyServices
CoServID (pk)
CoID (fk from tblInsCompanies)
ServID (fk from tblServices)
UnitPrice
you don't need a category field in this table, because each service is
already linked to a specific category, in tblServices.
note that you can dispense with the CoServID primary key field, and use the
two foreign key fields as a combination pk. i prefer to not do that,
especially when the table's primary key will be used as a foreign key in
another table, as below.

tblPatients
PatID (pk)
PatFirstName
PatLastName
PatDOB (don't store a patient's *age*, because that keeps changing. store
the date of birth, and you can calculate the age whenever and wherever
needed.)
other fields that describe a patient only, no data about the services
received by a patient.

tblPatientServices
PatServID (pk)
ServiceDate
PatID (fk from tblPatients)
CoServID (fk from tblCompanyServices)
Cost
this situation calls for bending the normalization rule re duplicating
data. cost for each service should be stored in the service record, because
over time the price that each insurance company pays for a given service
will change - but the price paid for a specific service to a specific
patient in the past should not change, so it must be hard-coded in this
table.

but, much better than just going with the above design, suggest you read up
(or read more) on relational design principles before proceeding. correct
tables/relationships design is critical to successful database development,
so it really pays to invest the time up front to get it right. and, armed
with an understanding of relational design principles, you're the best
person to structure your database - because you know more about your
business process than you can hope to tell us in this forum. for more
information, see http://home.att.net/~california.db/tips.html#aTip1.

last but not least, this is an *extremely* simplified medical services
tracking/billing structure. if it truly meets your needs, that's fine; but
in a real-world medical practice, i have to think that one of the many
commercial products on the market would serve you better. you also need to
consider whether this, or any, setup you use is in compliance with the HIPAA
patient data security requirements (if you're in the USA, that is).

hth

Hi people.
I am attempting to develop a new database and I'm new on Access.
[quoted text clipped - 62 lines]

Sorry for my bad English, and thank you for any help in advance

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200707/1

Check out this link for info on how to use the section from one combo box to
limit the options in another:

http://www.databasedev.co.uk/filter_combo_boxes.html

Lauri S.