Basic Database Design

[Silvex]

Hi people.
I am attempting to develop a new database and I’m new on Access.
It will work (I hope!) like this:
Database has 4 Tables:
tblCategory (CategoryID;CategoryName;Description).
tblProducts (ProductID;ProductName;CategoryID; InsuranceID;UnitPrice)
tblInsurance (InsuranceID;CompanyName;Phone;Fax) and
tblClient (Date;ClientName;Category;Product;Insurance;UnitPrice)

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.
I want to create a Form where I insert this data:
1)The Form will look like this: Frm Client based on TblClient
Date: ___(Field Date)_______
Name: ___(Field ClientName)_____________________
Age :__(fldAge) ___
And perhaps other fields to identify the client ….
Category: __cboCategory____(Rowsource from tblCategory)
Product: __cboProduct___ (filtered from the cboCategory)
Insurance Company: ____cboInsurance____(Rowsource from tblInsurance)
UnitPrice: __txtBox __(which should automatically fills with the Price for
that product that the Insurance company that I selected previously is paying)
- AND where is where i'm stucked
2)In Date field I’ll type the date of the record.
3)I type the Clients Names (the same client will have different records for
different dates)
4)I type all the other fields that identify that particular Client
5)In the cboCategory I select the kind of work I did to that Client (Ex:
A-Consultation; B-Surgery; C-Exams ; D-Medications; …)
6)In the cboProduct appear the products filtered by cboCategory I
selected (Ex: A-Consultation = 1)Gyne 2)Obst 3)Urgency 4)… B-Surgery =
1)Laparotomy 2)Salpingectomy 3)Hernia repair 4)…. and so on.
7)Then when I select the Insurance Company on the cboInsuranceCompany
Ex: AXA Insurance; MedicalInsurance; Doctorhelp;….)
the Unit Price txtBox will automatically fills with the price that the
Insurance company pays for that product, regardless of the client I inserted.
Example1:
Date: 16-07-2007
Name: Silvex
Age:----
Category: Surgery
Product: Hernia repair
Supplier: AXA
Unit Price: fills automatically according with the price AXA pays for Hernia
Repair
Example2:
Date: 25-06-2007
Name: Carol Stwart
Age:42
Category: Consultation
Product: Gyne
Supplier: Medical Insurance
Unit Price: fills automatically according with the price Medical Insurance
pays for Gyne Consultation
Example3:
Date: 5-07-2007
Name: Catherine Boulevard
Category: Surgery
Product: Hernia Repair
Supplier: Medical Insurrance
Unit Price: fills automatically according with the price Medical Insurance
pays for Hernia Repair

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

 

[jahoobob via AccessMonster.com]

Do you have a question? I didn't see one in your post.

 

[Chris2]

Hi people.
I am attempting to develop a new database and I’m new on Access.
It will work (I hope!) like this:
Database has 4 Tables:
tblCategory (CategoryID;CategoryName;Description).
tblProducts (ProductID;ProductName;CategoryID; InsuranceID;UnitPrice)

Silvex,

I see that each product may only have one category. This may be what you intended, but in
most businesses products can have more than one category, so I just wanted to point this
out.

If it turns out that your products may have multiple categories, you will need a new
table, tblProductCategories (or, preferrably, ProductCategories), that has:

ProductCategoryID -- PK
ProductID --\
CategoryID -- 2-column unique index.

tblInsurance (InsuranceID;CompanyName;Phone;Fax) and
tblClient (Date;ClientName;Category;Product;Insurance;UnitPrice)

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.
I want to create a Form where I insert this data:
1)The Form will look like this: Frm Client based on TblClient
Date: ___(Field Date)_______
Name: ___(Field ClientName)_____________________
Age :__(fldAge) ___
And perhaps other fields to identify the client ….
Category: __cboCategory____(Rowsource from tblCategory)
Product: __cboProduct___ (filtered from the cboCategory)
Insurance Company: ____cboInsurance____(Rowsource from tblInsurance)
UnitPrice: __txtBox __(which should automatically fills with the Price for
that product that the Insurance company that I selected previously is paying)
- AND where is where i'm stucked

Forms questions belong in:

microsoft.public.access.forms
microsoft.public.access.formscoding

Example1:

Example2:

Example3:

<snip example 3>


I most sincerely hope that none of that is private medical information that belongs to
real people, but it sort of looks real, which makes me wonder . . .


Sincerely,

Chris O.

 

[Silvex]

Hi! jahoobob
Thanks for your reply.
Probably I wrote to much and I did'nt explain well. Sorry.
My problem ( where I'm stucked..) is how to fill the 3 comboBox in the
formClient
in order to automatic fill the TxtBox UnitPrice after update the cboInsurance,
with the price for that Category/Product/InsurranceCompany.
Thus this make any sense to you !!! Hope so!!
Regards
Silvex

Do you have a question? I didn't see one in your post.

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

 

[Silvex]

Thanks Chris2 for your concern.
Don't worry, the examples I give are not real. I invented them just for
better expose my doubts
OK.
The products only belong to a Category
One Category - several products
One Product - One Category
I adress this in this group because I think is a question of
Tables/Forms/queries so Database Design
Sorry if I'm wrong.
regards
Silvex

Hi people.
I am attempting to develop a new database and I’m new on Access.
It will work (I hope!) like this:
Database has 4 Tables:
tblCategory (CategoryID;CategoryName;Description).
tblProducts (ProductID;ProductName;CategoryID; InsuranceID;UnitPrice)

Silvex,

I see that each product may only have one category. This may be what you intended, but in
most businesses products can have more than one category, so I just wanted to point this
out.

If it turns out that your products may have multiple categories, you will need a new
table, tblProductCategories (or, preferrably, ProductCategories), that has:

ProductCategoryID -- PK
ProductID --\
CategoryID -- 2-column unique index.

tblInsurance (InsuranceID;CompanyName;Phone;Fax) and
tblClient (Date;ClientName;Category;Product;Insurance;UnitPrice)

[quoted text clipped - 15 lines]

that product that the Insurance company that I selected previously is paying)
- AND where is where i'm stucked

Forms questions belong in:

microsoft.public.access.forms
microsoft.public.access.formscoding

Example1:

Example2:

Example3:

<snip example 3>

I most sincerely hope that none of that is private medical information that belongs to
real people, but it sort of looks real, which makes me wonder . . .

Sincerely,

Chris O.

 

[Chris2]

I adress this in this group because I think is a question of
Tables/Forms/queries so Database Design
Sorry if I'm wrong.
regards
Silvex

Silvex,

You're not wrong to post here. Forms questions are posted here, and get answered here,
all the time.

I was just making a recommendation.

As for the table designs, the first and third look ok.


As for the second, tblProducts, InsuranceID needs to be split out into a new table.

ProductInsurance
ProductInsuranceID -- PK
ProductID -- FK to Products (ProductID) -----\
InsuranceID -- FK to Insurance (InsuranceID) -\
ProductInsuranceDate -------------------------- 3-column unique index.


As for the fourth, tblClient, it combines client data with product, category, and
Insurance data all at the same time.

Category information is already contained in tblCategory and tblProduct

You need three separate tables, Clients, ClientProducts , and ClientInsurance.

Clients
ClientID -- PK
ClientNamePrefix
ClientNameFirst
ClientNameMiddle
ClientNameLast
ClientNameSuffix

ClientProducts
ClientProductsID -- PK
ClientID --- FK to Clients (ClientID) --\
ProductID -- FK to Products (ProductID) -\
SaleDate --------------------------------- 3-column unique index.

ClientInsurance
ClientInsuranceID -- PK
ClientID --- FK to Clients (ClientID) -------\
InsuranceID -- FK to Insurance (InsuranceID) -\
InsuranceDate --------------------------------- 3-column unique index.


I personally would use InsuranceStart and InsuranceEnd, so I would know how long they were
insured under a particular insurance. (And probably two dates for ProductInsurance, as
well.)


Oh, I just noticed I stopped using the "tbl" prefixes in the middle of writing that.


Also, you have the UnitPrice column in both tblProduct (where it appears to belong), and
in tblClient (where it does not belong, as UnitPrice does not describe a client). I would
also add at least one date column to tblProduct, so you can track when prices change.


Sincerely,

Chris O.

 

[Silvex via AccessMonster.com]

Chris Thanks again.
I will try to do the design that you have send, and will "post" you a note in
order to tell you if it worked. OK
Regards
Silvex

 

[Chris2]

Chris Thanks again.
I will try to do the design that you have send, and will "post" you a note in
order to tell you if it worked. OK
Regards
Silvex

Silvex,

The table designs I provided were suggestions.

You need to be able to review them for applicability to your situation, and modify or
extend them as necessary.

To do that, you'll need some further knowledge on the subject of database design.


Books: General: Beginner

Database Design for Mere Mortals by Michael J. Hernandez

SQL Queries for Mere Mortals by Michael J. Hernandez, John L. Viescas


Database Normalization:

Basics:

About.com
http://databases.about.com/od/specificproducts/a/normalization.htm

Tips to Get You Going
http://home.att.net/~california.db/tips.html#aTip1

Microsoft: Description of database normalization basics in Access 2000 (not significantly
changed by Access 2007, see the article's own references at the end to material from the
early 1990s).
http://support.microsoft.com/support/kb/articles/q209/5/34.asp


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html


Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization


Very Advanced:

University of Texas:
I like this whole site, since it has a handy menu on the right describing many important
aspects of the database world:
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html


Sincerely,

Chris O.

 

[Silvex via AccessMonster.com]

Shi! Chris.
Now you really scare me.
I thought it was a little and basic thing to do.
I have no time to study all that books but I will visit those sites you post
me.
I was already thinking if the struture and the alterations that you send me
previously, where enought
to solve my problem whitch is, 3 comboBoxes in the form (cboCategory,
cboProduct,cboInsurance)
On cboCategory I select the Category .One category have several products, so
on the CboProducts I will select the proctuct from the Category I select
previously ( I think I know how to do the code for filter cboProducts to the
Category selected on cboCategory). Now, the Client has a Insurance Company
(always the same, doesn't change). There are various Insurance companies and
all fournish all the products at diferent prices. What I wish is that when I
select the Insurance company (on the third comboBox - cboInsurance) that the
client I'm recording is associate, the price that this company pays for the
product I select on the cboProduct, will automatically appears on the
UnitPrice Txtfield.
regards
Silvex

 

[tina]

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

 

[Silvex via AccessMonster.com]

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

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

 

[LauriS]

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.