Database design problem

[smshaaban]

Hi there.
I am building a database to hold the information of my private clinic. We are two doctors, each having his own patients. We also provide some imaging services (ultrasound, caridac echo..), which are performed by other physicians.
I created a table with patient info, linked it to a 'patient visits' table, but I stumbled on a problem. Before creating the DB, me and my partner used the same numbering system (starting with patient number 101, going to 102..etc), with separate folders for our paper files. So if I want to make a master patient ID for the clinic containing all patients (mine and my partner's), there will be duplicate numbers. I gave it a try by creating an auto-number field and making the original numbering as a separate field, only to find out that my no. 110 patient is linked to my partner's 110's patients visits. Given the fact that I can't ditch the old numbering system, I was wondering: how can I make this master ID table containing all our patient's, after which I can use the 'visits' table to classify them according to visit types to make the DB more efficient instead of making a patient ID table for every service which would lead to duplicates (a patient can see me, and then do an ultrasound by Doctor X for example)?
Could the answer be a custom ID? something like 110-A, where the suffix 'A' refers to doctor A, B to doctor B ans so on? An how can I do it?
I'm not familiar with VB codes..so bear with me

Sorry if I sound ambiguous. I really appreciate your help.

 

[MarkK]

Don't build these fancy keys. They add no value and they are a headache. In an Access table just make an Autonumber primary key, which is an auto-incrementing long integer.

Abandon all other numbering systems as functionally redundant. Any other numbering system will require work to maintain, and will return no value for that effort.

Also, a "visit" should describe the patient, the doctor, and the location in space and time. As such, you should have a doctors table, maybe a rooms table, definitely a patients table.

Hope this helps,

 

[ButtonMoon]

Don't build these fancy keys. They add no value and they are a headache. In an Access table just make an Autonumber primary key, which is an auto-incrementing long integer.

Abandon all other numbering systems as functionally redundant. Any other numbering system will require work to maintain, and will return no value for that effort.

I don't think that's very practical advice. Autonumber columns are generally designed to be used as surrogate keys only. I have no objection to their use as surrogate keys but autonumbers don't mean you can abandon natural keys - i.e. the external identifiers actually used to identify real things in the real world. An often-stated principle of autonumbers is that they are "not for human consumption" - but human users of the system clearly will need identifiers that are people-friendly (Doctors need to be able to identify their patients; HR departments need to identify employees, etc).

An autonmber would usually be a bad choice as the key used as a "natural" domain key identifier. In fact, in many health care applications there are legal, government or organisational standards that mandate what patient identifiers must be used. Keep autonumbers for surrogate key use only, as and where you need surrogates.

smshaaban, I'll assume you do need a patient identifier and not just an autonumber column. Think carefully about the implications of putting the doctor ("A","B",etc) code as part of the identifier. Consider what you would do if a patient changes doctors: change their patient ID or accept that the ID is no longer reliable as a way of identifying a patient's doctor? Only you can decide what works for your organisation but you at least need to think about those scenarios when defining a coding scheme like this.

 

[smshaaban]

Thanks, ButtonMoon. You are right about your concerns. A patient can change doctors. He can also see me and then undergo an ultrasound scan by Dr.B. He can see Doctor C for a problem outside my specialty. So I need the database to hold all this data efficiently without having to repeat data.
Would you suggest removing the old patient identifier we were using and replacing it with a 'global' ID (autonumber) in the 'patientID' table, and then use 'Doctors', 'visits' and "exam_type" tables to save the visit type for the individual patient, which would help consolidate his visits to different services in one place?
Thanks.

 

[gemma-the-husky]

seriously - if you are a doctor, it will surely be much more economical for you to pay someone to do this for you, than try and learn access yourself.

good luck, though!


the data analysis for this will be critical - your last post is an indication of the detail you need to think about to get this right.

 

[MarkK]

I would say, notice how much effort you are going to go through in the life of your database producing and managing these "human readable" codes, and asses how much value they add to your day to day operation. Where do you use them? On labels? Just print the information on the label that you need on the label. Why use some cryptic "human readable" key?

And you have to change the data, you have to change your fancy key.

So just use the data and forget about the key. Or not. But notice, and let me know in a year.