I'm designing a medical insurance billing data base that will only be used for billing public aid.
Tables:
1) client (with the basic info)
2)OfficeVisit (VisitID pk; DOS (date of service) ProcedureID, Client ID, DxID.
3) Procedure (ProcedureID pk; Code; Description)
4) Diagnosis (dxID, Code, description)
5) Invoice (InvID, Datebilled)
6) Fees (FeeID, Amt)
Problems:
1) Table: "Procedure" MOST of the procedure codes have one standard description. However, another, "S4993" will have different descriptions. Specifically, this code represents a drug, but has to be billed with Manufacture's name and NDC # along with the quantity in the description field. There are several different manufacture names that will be used. The description for this procedure will fill two lines in the description field on the preprinted form.
Example: Procedure code S4993; Description Nordette #84 NDC 0008-1215-06. This can change with the # being 28 or 56; and the NDC # can change. Instead of Nordette, it could be Alesse, etc, etc. How can I address this in the table keeping in mind that the user will be using drop down boxes to choose entries.
2)Each procedure will have a specific fee. The S4993 procedure as mentioned in above, can have different fees based upon the # being 28, 56 or 84. How can I set up the Fee table for this?
3)Fee Table: As mentioned in #2, each procedure will have a specific fee. If in the future, the fees change, How can this table be updated with the new fees, without changing what has been previously billed.
I know I have a lot to be addressed. But any help will be appreciated.
Tables:
1) client (with the basic info)
2)OfficeVisit (VisitID pk; DOS (date of service) ProcedureID, Client ID, DxID.
3) Procedure (ProcedureID pk; Code; Description)
4) Diagnosis (dxID, Code, description)
5) Invoice (InvID, Datebilled)
6) Fees (FeeID, Amt)
Problems:
1) Table: "Procedure" MOST of the procedure codes have one standard description. However, another, "S4993" will have different descriptions. Specifically, this code represents a drug, but has to be billed with Manufacture's name and NDC # along with the quantity in the description field. There are several different manufacture names that will be used. The description for this procedure will fill two lines in the description field on the preprinted form.
Example: Procedure code S4993; Description Nordette #84 NDC 0008-1215-06. This can change with the # being 28 or 56; and the NDC # can change. Instead of Nordette, it could be Alesse, etc, etc. How can I address this in the table keeping in mind that the user will be using drop down boxes to choose entries.
2)Each procedure will have a specific fee. The S4993 procedure as mentioned in above, can have different fees based upon the # being 28, 56 or 84. How can I set up the Fee table for this?
3)Fee Table: As mentioned in #2, each procedure will have a specific fee. If in the future, the fees change, How can this table be updated with the new fees, without changing what has been previously billed.
I know I have a lot to be addressed. But any help will be appreciated.
