Easy Database | Undoable for a Newbie

[Hans-Jürgen Strecker]

Dear All, dear Oracle,

how great it is to have a a Newbie Forum and THANK YOU ALL FOR IT and your time helping.

The last time I was in contact with databases was in school doing some MS ACCESS.

Since then a lot might have changed, but the general rules stayed the same I assume.

I am currently working for a small company (an MD) in the health care sector in which a very old Delphi language software is being used .

to get more information out of the existing data I tried to set a BI on top of this software but it did not work as there is no real DB structure.

therefore I decided to build a MySQL databases from scratch.

I installed the MSQL server on a Windows Server 2016 and the Workbench as well.

I am connected to to the server via VPN from home and with the Workbench on my Macs. That all works out fine.

I created a schema and the first table. but now it comes to the best possible structure of the database and what I would like to get out of it.

I do not like to describe the data because I hope that someone would provide a solution (asking for help is one thing, asking for work to be done a NOGO for me).

So my general goal would be to get some sources to freshen up my database way of thinking. so the best learning material available right now.

I was looking through a ton of videos but there is a missing link somewhere because the info of how to structure my tables I was not able to find.

maybe because I guess it is so easy that no one talks about about.

What data do I have:

I have several different health insurances that all offer the same services but with different codes.

HI 1: ECG recording has the Code 123
HI 2: ECG recording has the Code 456
HI 3: ECG recording has the Code 789

so the ECG recording is one of my products, having a PK being connected to one of my health insurances.

so would I make one table for HIs and one table for the products of each HI

Furthermore the products do have different prices in the different HIs as well as different prices within each HI in different time brackets.

so in HI 1 the ECG had a price of EUR 1,00 from 02/2015 - 11/2015 and of EUR 1,10 from 12/2015 - 01/2017 etc. etc.

the pricing periods are not the same in each HI, so e.g. HI 2 has a price of EUR 0,80 from 05/2015 - 06/2018

BUT if the prices change, the prices for ALL products in this HI change.

would that mean I need a table for all products per HI for each time range? Does that make sense?

I will then after I have setup all the infos enter the products delivered on a monthly basis in terms of EUR value and number of products.

so for each HI and each product what we have delivered

year: 2016
month: January
product: ECG
HI: 1
product code respective to this HI: ECG_HI 1
amount of products: 25
EUR value per product at this date: 1,1 EUR
total amount: 27,5


this is the information I would like to have in the end.
in which HI I earn which amount in which time with which product.

it sounds so easy.


COULD SOMEONE PLEASE LET ME KNOW WHERE TO FIND THE BEST LEARNING MATERIAL TO STRUCTURE THIS.

thank you so much already for your time reading through this!