Database design

[Wictorian]

I dont have much experince with databases and I need to build one. Any advice?

 

For context: 

I need 3 databases

 

1 for people that will contaion some personal info and prescriptions

 

1 for prescriptions that will contain drug names

 

1 for drugs that will contain names and prices

 

[Quickstrike]

2 minutes ago, Wictorian said:

I dont have much experince with databases and I need to build one. Any advice?

 

For context: 

I need 3 databases

 

1 for people that will contaion some personal info and prescriptions

 

1 for prescriptions that will contain drug names

 

1 for drugs that will contain names and prices

 

This is not a big job and you shouldn't do it yourself if you have no clue about what you are doing.

This is going to be private data and your liability in case of a breach is way higher than paying a programmer to build something secure

 

[BuckGup]

2 minutes ago, Quickstrike said:

This is not a big job and you shouldn't do it yourself if you have no clue about what you are doing.

This is going to be private data and your liability in case of a breach is way higher than paying a programmer to build something secure

 

Little trigger happy I would say. The OP isn't providing any data and as far as we know it's for a school assignment. 

 

I would sit down and create a UML diagram so you can determine inheritance, private data, keys, and how to efficiently join them if needed. It's much easier to see the flow of data before being balls deep not knowing what's happening 

[Eigenvektor]

8 hours ago, Wictorian said:

I need 3 databases

1 for people that will contaion some personal info and prescriptions

1 for prescriptions that will contain drug names

1 for drugs that will contain names and prices

You need one database and multiple (database) tables, not multiple databases. Look at the relations between your data.

 

First, you have a person with some personal information. So something like

Person: { PersonId, First Name, Last Name, Age, Sex, … }

 

Then you have a prescription. A person can have multiple prescriptions, but a prescription can only belong to one person, so 1:n. This is easily done with a foreign key in the prescription table

Prescription: { PrescriptionId, PersonId, DisplayName, … }

 

Then you have drugs. A drug can belong to multiple prescriptions and a prescription can contain multiple drugs, so n:n. This requires an additional relationship table. Technically your relationship table does not need its own ID, you could just use the combination of both other IDs as its key. However it can make certain things easier to do, e.g. its often easier in SQL scripts if you don't have to deal with a composite key.

PrescriptionDrug: { PrescriptionDrugId, PrescriptionId, DrugId, Dosage, Duration, … }

 

Last you have the drug, which like person is a simple table without any relationship information

Drug: { DrugId, Name, Manufacturer, Price, Currency, … }

 

[mariushm]

Start with something simple like SQLite , or go directly with MariaDB (MySQL alternative) 

For both you have applications that let you create users, databases, tables etc from a simple interface, and show you the actual SQL command used to perform those operations. 

For example, MariaDB comes bundled with HeidiSQL which you can also download separately: https://www.heidisql.com/ . SQLite has lots of free programs, for example SQLiteStudio is one of them : https://sqlitestudio.pl/

 

it would make more sense to have that information as tables into a database. 

 

Keep in mind that you may want to have medicine info and price + supplier separately - you could have multiple suppliers for the same medicine 

Also at medicine you can have one drug in different packages ex slow release, fast release, 1mg pills, 5mg pills 10mg pills , introduced in body as injection or as suppository - basically what I'm saying don't make the title of the medicine a unique key, because you could have in the table several entries with the same medicine name. 

medicine name ,  units per package (ex 30 pills in blisters, or 10 ampules) , active ingredient amount 5ml 10ml whatever , manufacturer (could be an ID pointing to another table with manufacturers)

 

 

 

 

[Wictorian]

3 hours ago, Eigenvektor said:

You need one database and multiple (database) tables, not multiple databases. Look at the relations between your data.

 

First, you have a person with some personal information. So something like

Person: { PersonId, First Name, Last Name, Age, Sex, … }

 

Then you have a prescription. A person can have multiple prescriptions, but a prescription can only belong to one person, so 1:n. This is easily done with a foreign key in the prescription table

Prescription: { PrescriptionId, PersonId, DisplayName, … }

 

Then you have drugs. A drug can belong to multiple prescriptions and a prescription can contain multiple drugs, so n:n. This requires an additional relationship table. Technically your relationship table does not need its own ID, you could just use the combination of both other IDs as its key. However it can make certain things easier to do, e.g. its often easier in SQL scripts if you don't have to deal with a composite key.

PrescriptionDrug: { PrescriptionDrugId, PrescriptionId, DrugId, Dosage, Duration, … }

 

Last you have the drug, which like person is a simple table without any relationship information

Drug: { DrugId, Name, Manufacturer, Price, Currency, … }

 

What I had trouble with was adding a primary key unlimited drugs to prescriptions. What I had in my mind was to add the primary key to the first row and then add drug info. (Actually just the names are pretty much enough.)

[Franck]

15 hours ago, Quickstrike said:

This is not a big job and you shouldn't do it yourself if you have no clue about what you are doing.

This is going to be private data and your liability in case of a breach is way higher than paying a programmer to build something secure

 

Agree, i really hope it's homework otherwise get a good lawyer on this first. Unless the country you are in have nearly no laws or something.

[Wictorian]

1 hour ago, Sakuriru said:

What you think he's running a pharmacy in his backyard? lol

 

@Eigenvektorhas you off to a good start. If you give me a bit I can give you a full example from spinning up the DB to creating the tables and indexes, managing relationships, and querying the data.

 

I'm not sure which DB engine you're using but it's relational so that's a start. My favorite is postgres but for standalone applications SQLite is often used.

I was planning to use python and mysql

[Takumidesh]

You should probably study up on the relation part of relational databases. one-one, many-many, many-one.

 

You can use the db engines admin tools to create the database and tables with a gui if you are not super comfortable with SQL, then you just need to worry about the CRUD stuff.

 

 

[wanderingfool2]

6 hours ago, Wictorian said:

I was planning to use python and mysql

What @Eigenvektor said...but I would like to preface this with the fact that just implementing things is different from actually knowing things.

 

Please don't take this the wrong way, but still based on the topics you are creating it really appears as though you a fragmenting yourself and not learning the fundamentals of what it means to try developing.  Like it's okay to ask questions, and ask how to do things, but you have an innate ability to ask questions while not giving enough information to help you on your journey properly (and you appear to be trying to learn too much of different areas all at once).

 

I honestly would suggest completing things such as your calculator first, learn from it, and then moving onto a project like you are planning.

[Wictorian]

8 hours ago, wanderingfool2 said:

What @Eigenvektor said...but I would like to preface this with the fact that just implementing things is different from actually knowing things.

 

Please don't take this the wrong way, but still based on the topics you are creating it really appears as though you a fragmenting yourself and not learning the fundamentals of what it means to try developing.  Like it's okay to ask questions, and ask how to do things, but you have an innate ability to ask questions while not giving enough information to help you on your journey properly (and you appear to be trying to learn too much of different areas all at once).

 

I honestly would suggest completing things such as your calculator first, learn from it, and then moving onto a project like you are planning.

Thanks for your feedback, I don’t think you are wrong, however I don’t think this is the case with implementing / learning. Maybe I have issues about implementing because of my lack of experience and nonetheless it creates a problem..

[shadow_ray]

19 hours ago, Sakuriru said:

What you think he's running a pharmacy in his backyard? lol

well.. if there is a huge demand for painkillers and sedatives in the neighborhood that's a great business opportunity

Understanding customer behavior and purchasing patterns is essential for increasing sales and a database can help with that.

 

sorry... i might be under the influence of some... pills i got from the local backyard pharmacist

[Wictorian]

10 hours ago, Sakuriru said:

Installation and setup

 

We can start by installing MySQL so we'll run it for development work. Personally, I prefer using winget for all of my installs, but this only really installs the installer, you can also get it the old fashioned way from here. MySQL is going to ask you for a bunch of stuff, just set it up for development work and keep the defaults. I used the basic password "mysql" since I'm not really interested in using it for anything except a localhost database.

 

 

This should start MySQL and start it running on your local machine. Mine used 3306 by default. It should be running as a Windows service so you only need to open up MySQL Workbench (bundled with the MySQL install) to work with the database engine. I use DataGrip professionally, and greatly prefer it, but something like this will be fine for our purposes.

 

The first thing to note about databases is that MySQL is the database engine and can store multiple databases, or schemas, within it. You could think of a database schema as a database namespace, and they're completely separate from other ones. I created a database called development by running the statement

CREATE DATABASE development;

in the query window. After it has been successfully created we need to still connect to that specific database.

 

From here we're off to the races and the database is ready to be used.

 

Creating the database objects

Typically it's a good idea to napkin out your database with some casual links just to get a visual feel for how the data looks and how it's going to be all connected. This is probably going to be confusing for you if you haven't been introduced to the concept of normalization, but suffice to say you can think of database tables like excel spreadsheets, with defined rules about how each sheet works.

 

Using Eigenvektor's schema as a base, I drafted up a simple diagram to show the relationships between different tables (or entities, as they're called).

 

I've removed some of the columns that I felt weren't really necessary for a demonstration. Granted it may be better to add more in the future for something that functions better, but this is a good start with a solid schema. Typically you would design a database the same way real life works: a person can have multiple prescriptions, and each prescription can have multiple drugs in it. That's encapsulated in the design above.

 

The types on the right-hand side are MySQL data types. In relational databases, you have to specify exactly which columns hold what data and sometimes the maximum size of that data. This is what allows the database to determine how to store that information. The bytes have to be stored somewhere somehow, and by using appropriate types you can lower the size of the database and make it faster to search.

 

That said, in practice it would look like this

 

  Reveal hidden contents

CREATE TABLE Person (
    PersonId int auto_increment primary key,
    FirstName varchar(100),
    LastName varchar(100)
);

CREATE TABLE Prescription (
    PrescriptionId int auto_increment primary key,
    PersonId int,
    DisplayName varchar(255)
);

CREATE TABLE PrescriptionDrug (
    PrescriptionDrugId int auto_increment primary key,
    PrescriptionId int,
    DrugId int
);

CREATE TABLE Drug (
    DrugId int auto_increment primary key,
    `Name` varchar(255),
    Manufacturer varchar(255),
    Price decimal(10, 2)
);

 

Where we have defined the tables in terms that MySQL can understand. This is just SQL at this point, which is a common programming language used for relational databases. It's going to feel more like a scripting language and each database engine has their own quirks about how these all work together. In MySQL this is what it looks like. A few notes about the above:

• int stands for integer, but auto_increment and primary key are special identifiers. Auto increment will automatically manage incrementing the PersonId column on the Person table on row insertion, so we don't have to worry about tracking it ourselves. Primary key is telling the database that the rows are both unique off of this number (no other row shares it) and to index off of it. Indexes are beyond the scope of this post but suffice to say looking things up by PersonId are faster than by LastName.
• The varchar(255) is specifying the size in characters that the column can store up to. 100 is usually plenty for names, but 255 is standard for short descriptions. There's ways to store longer text in databases, but varchar is special in that it's stored in row and not stored in some blob elsewhere because it has indeterminate size.
• The decimal(10, 2) is specifying 10 digits with 2 places of precision for the decimal type. This is important way to store monetary amounts because floats and doubles are not exact and can cause rounding or calculation errors if you try to compare exact amounts.
• The backticks `` around `Name` are just MySQL syntax to allow you to refer to an identifier literally. Normally "name" is a reserved keyword for the programming language, but surrounding our column name in backticks allow us to specify that it's an identifier and not a keyword.

 

Seeding the database

A database isn't going to be much fun without any data in it to play with, and normally there's always some kind of data that needs to be loaded first. This is called "seeding" the database. I've supplied some fake data with which to get started messing around with it

  Reveal hidden contents

INSERT INTO Person (FirstName, LastName) VALUES
('Tim', 'Timothy'),
('Pat', 'Patricia'),
('Bob', 'Robert');

INSERT INTO Drug (`Name`, Manufacturer, Price) VALUES
('Tylenol', 'Johnson & Johnson', 4.99),
('Aleve', 'Bayer',  5.99),
('Aspirin', 'Bayer', 2.99);

INSERT INTO Prescription (PersonId, DisplayName) VALUES
(1, 'Tim\'s Prescription'),
(2, 'Patricia\'s Prescription'),
(3, 'Bob\'s Headache Prescription'),
(3, 'Bob\'s Heart Prescription');

INSERT INTO PrescriptionDrug (PrescriptionId, DrugId) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 2),
(3, 1),
(3, 2),
(4, 3);

This is just what the data looks like as flat tables. It's going to be difficult to really tell what's going on here, especially in the PrescriptionDrug table, until we build some queries.

 

Querying and creating a view

I won't be going into SQL for querying just yet, you can play around with that for yourself. But suffice to say to combine all of this data into something that makes sense from your perspective can be achieved by doing something called joining, where we combine rows from tables and piece them together to get a row of data that we want.

 

This is a query that I created to view a person's name, the name of their prescription, the drugs in that prescription, and the price of it. Notice how some of the columns have duplicated data. That's because of the one to many relationship being talked about earlier. There's only one person for many prescriptions, and only one prescription for many drugs.

 

Any particular query can be stored in the database as something called a view, which allows you to use the query as though it were a table.

  Reveal hidden contents

CREATE VIEW PersonPrescriptions AS (
    SELECT 
        (select concat(p.FirstName, ' ', p.LastName)) as `Name`,
        ps.DisplayName as Prescription,
        d.`Name` as Drug,
        d.Price as DrugPrice
    FROM PrescriptionDrug pd
    JOIN Prescription ps ON ps.PrescriptionId = pd.PrescriptionId
    JOIN Person p ON p.PersonId = ps.PersonId
    JOIN Drug d ON d.DrugID = pd.DrugId
);

 

But an important thing to keep in mind is that a view is not actually a table, and the database has to join the data each time its queried to build the dataset. It won't ever be as efficient as querying a table directly.

 

Getting your data into python

We've covered quite a bit by now, but now here's the juicy bit. You need just to install some packages and write some boiler plate and you'll be good to go. Most people have already done all the hard work for you.

 

SQLAlchemy and the PyMySQL packages are all you really need to connect.

from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:mysql@localhost/development")
conn = engine.connect()

result = conn.execute("SELECT * FROM PersonPrescription;").fetchall()
for r in result:
    print(r)

 

Here we have a connection string, which in this case is just passing the password in the string. Normally you wouldn't do this, but this is just development so it's fine. In this case, you can just run text strings as queries to get your desired result.

 

There is also something called ORM, or Object Relational Mapping, which allows you to relate classes to database entities. This is also what .NET entity framework uses and is a pretty popular way to manage models in enterprise systems. Databases can get complicated, and managing what data it has where and all the rules for it can be encapsulated on your backend with an ORM. SQLAlchemy also includes ORM features, but it's beyond our scope so suffice to say it's probably better to stick to conn.execute() and transaction.commit() functions for now.

 

That should get you dangerous. This really blazed over quite a bit and you'll probably have a lot of questions, do a lot of research and tinker around with it. Relational databases normally take up an entire semester in university so if it feels like a lot, it's because it is. But don't worry it's all designed to make sense. Good luck!

 

---

 

Github

Thanks a lot this was really comprehensive.

 

Why use join over nested queries though?

[Franck]

4 hours ago, Wictorian said:

Why use join over nested queries though?

Efficiency. A nested query doesn't resolve like a join and have uncertainties in the search path while a join still have it's own problems it is made for linking relations like this. Specially if you have conditions they can be interpreted during the result building sequence making it way more performant.

[reniat]

On 9/29/2022 at 1:41 PM, Wictorian said:

1 for people that will contaion some personal info and prescriptions

Is this for a school project with pretend data, or is this for a real set of patient data? If your company is having you make this database for REAL patient data and you don't have the knowledge to do that securely, then that feels like a massive HIPAA violation waiting to happen.

There's nothing wrong with not having database experience, but there is something wrong with having someone without experience make a database for this kind of data.

[Takumidesh]

1 hour ago, reniat said:

Is this for a school project with pretend data, or is this for a real set of patient data? If your company is having you make this database for REAL patient data and you don't have the knowledge to do that securely, then that feels like a massive HIPAA violation waiting to happen.

There's nothing wrong with not having database experience, but there is something wrong with having someone without experience make a database for this kind of data.

based on OPs various posts around the forum, I would assume that it is just a learning project.