I am currently thinking about simplifying a database constructed by a prior employee.

It has basically the cost of service provisions by doctor surgeries (practice). The tables I have are as follows:

Tble_querynames

ID_Queryname(PK)
Queryname


Tble_fieldnames

ID_Queryname
Queryname
Fieldname(PK)


Dbo_es_factbase
Fieldname
Gppracticecode


Tbl_practice
Practicecode(PK)



Each query can have many fields
Each field can have many records in the factbase table
Each practice can also have many records in the factbase table


The complication is that each record in the factbase table has a total number of patients which have an associated cost. This cost can change at any time.

I am not sure whether to have an M-2-M relationship because each fieldname will have potentially many associated costs but a cost would not be associated with many fields.

Should I create an M-2-M relationship and how would I achieve this?

I don't really understand the following two tables: Tble_querynames and Tble_fieldnames. It sounds like they contain names of queries in the database and the fields associated with those queries. Do they contain any actual data relative to doctors, patients or surgeries?

Tble_querynames

ID_Queryname(PK)
Queryname


Tble_fieldnames

ID_Queryname
Queryname
Fieldname(PK)

These fields are related to names in the factbase table. You do have a point but that is not the issue of this thread!

Can you provide more info about your application? More info about each of the tables and fields. And just plain English as to what the processes are and what you would like it to do differently.

Without knowing more about the tables that are pertinent to your question, it is hard to help. So I agree with jdraw in that we need more info.

The database is a record of aggregate patient activity. The ‘factbase’ table is downloaded every quarter with the new updated information from doctor surgeries in our region.

The 'factbase' table is a list of thousands of records of total number of patients by ‘queryname’ (which is the service, eg alcohol management, drug misuse etc) and ‘fieldname’ (which are the column names for each individual service).

This information is downloaded from an ODBC server so maybe I ought to ask them to change these two fieldnames. I agree it is confusing. I only named them the same to minimize the confusion.

Each doctor surgery can provide many services and each service can be provided by many surgeries so the ‘factbase’ table is the middle table in the M-2-M relationship.

So I hope my first message makes a bit more sense.

All I would have to do is multiply the cost by the number of patients to get the total. The crux of my problem is dealing with price changes. Each service activity has a cost attached to it but this may change at any time.

I presume I would need an M-2-M relationship between a costs table and the ‘factbase’ table but how can I identify the appropriate record in the ‘factbase’ table with the appropriate cost?

I am concerned by the following (if I understand it correctly, that is):

...which are the column names for each individual service..

If each service is its own separate column (i.e. field) in the table that would mean the table is not normalized and trying to associate a price for each service would be difficult. Trying to associate price changes would be next to impossible.

If I am right (please correct me if I am wrong!), then my recommendation is to create a totally new, normalized structure and move the information into that new structure each quarter and get rid of factbase table once the move is complete.

Hello,

I didn't quite explain myself clearly (apologies once again!) :confused:

I have attached a screenshot of the table relations I have at the moment.

In the 'factbase' table, the 'queryname' field lists all the services and the 'fieldname' field lists all the indicators. The excel file should make this clearer with an example for one surgery practice indicated by the K-code.

Eg CHDIMMA is child-immunisations and the queryname lists the number of patients associated with each indicator.

I think the cost information could be a little bit more complex for some services more than others. At the moment, I am happy to work on a simplified version and take it from there.

This is what I am thinking...

Assuming that a practice can charge different prices for the same service compared to another practice, you need to related the service and practice together. I assume also that the practices are held in tbl_Practice

tblPracticeService
-pkPracticeServiceID primary key, autonumber
-fkID foreign key from fieldnames table
-PracticeCode foreign key to tbl_Practice

Also, I assume prices can change over time, so we need a table to track those changes

tblPracticeServicePrices
-pkPracServPriceID primary key, autonumber
-fkPracticeServiceID foreign key to tblPracticeService
-dteEffective (effective date of the price)
-currServicePrice

Hello,

It isn't quite that complicated because the cost for each service is the same for every practice at any given time.

Having said that I am not sure whether the cost comes into effect immediately or from the start of the next quarter.

It isn't quite that complicated because the cost for each service is the same for every practice at any given time.


Do you mean that the price never changes during the middle of a quarter?

Are you basically starting over each quarter with new data? What happens if you need to go back to look at data from 2 quarters ago or any past quarterly data? I would think it would be better to take the data in your factbase table and migrate it into an appropriate structure each quarter (then get rid of the factbase table). That way you can look at historical as well as the current data. With that scenario, the pricing table I suggested would make more sense.

Having said that I am not sure whether the cost comes into effect immediately or from the start of the next quarter. This sounds like a question you have to resolve in order to associate the right price at the right time for a service. The outcome will impact how you structure the table/tables.