If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Basic Database Design
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. |
|
Thread Tools | |
Display Modes | |
|
|