Related tables. SimpleSqlite extension. Pharmacy example

1.- Sqlite related tables.

p55H_Sqlite_Farmacia.aia (34.9 KB)

  • Most databases can use related tables.
    There are several types:
    one by one
    one to many
    many to one
    many to many

https://database.guide/what-is-a-relationship/

We are going to create a relationship between two tables of type: many to many.

  • We have a Sqlite: farmacia.db
  • With two tables: pacientes y vademecum

  • A patient can have several medicines.

  • One type of medicine can be assigned to several patients

  • I have used the SimpleSqlite extension by @TIMAI2

This example with MySQL:
https://community-appinventor-mit-edu.ezproxy.canberra.edu.au/t/related-tables-mysqli-pharmacy-example/89190

4 Likes

2.- Load farmacia.db. Insert. Update. Delete.

  • We can copy the farmacia.db file from Asset to the ASD (contains data for testing).
  • Open DataBase.
  • We can insert, update and delete data in the pacientes and vademecum tables.

  • Note that we use three tables: pacientes, vademecun and pacientes_vademecum (in the latter the relationship is made).

  • The relationships and visualization will be done on Screen2.

1 Like

3.- Create relationships. Show.

  • Through the CheckBox we can show a list of pacientes or vademecum.

  • We choose a paciente. We chose a vademecum. Through "Add medicine selected to this name" we associate a paciente with a vademecum.

  • "Show medicina for nombre": It shows us the different medicines assigned to a nombre.

  • "Show nombre for medicina": It shows us the different nombre that have been assigned to that medicine.

1 Like

4.- Codes used.

-- Create table pacientes
CREATE TABLE  IF NOT EXISTS pacientes (
  id INTEGER PRIMARY KEY,
  nombre TEXT,
  edad INTEGER
);

-- Create table vademecum
CREATE TABLE IF NOT EXISTS  vademecum (
  id INTEGER PRIMARY KEY,
  medicina TEXT,
  precio INTEGER
);

-- Create table intermediate pacientes_vademecum for relation many to many
CREATE TABLE IF NOT EXISTS  pacientes_vademecum (
  pacientes_id INTEGER,
  vademecum_id INTEGER,
  FOREIGN KEY (pacientes_id) REFERENCES pacientes(id),
  FOREIGN KEY (vademecum_id) REFERENCES vademecum(id),
  PRIMARY KEY (pacientes_id, vademecum_id)
);


-- Insert data into the pacientes table only if it does not already exist
INSERT INTO pacientes (nombre, edad)
SELECT 'Juan', 24
WHERE NOT EXISTS (
    SELECT 1 FROM pacientes WHERE nombre = 'Juan'
);

-- Insert data into the vademecum table only if it does not already exist
INSERT INTO vademecum (medicina, precio)
SELECT 'Aspirina', 251
WHERE NOT EXISTS (
    SELECT 1 FROM vademecum WHERE medicina = 'Aspirina'
);


-- Insert relation pacientes - vademecum
INSERT INTO pacientes_vademecum (pacientes_id, vademecum_id)
VALUES (
  (SELECT id FROM pacientes WHERE nombre = 'Juan'),
  (SELECT id FROM vademecum WHERE medicina = 'Aspirina')
);

-- Select medicina for Juan
SELECT v.medicina
FROM pacientes p
JOIN pacientes_vademecum pv ON p.id = pv.pacientes_id
JOIN vademecum v ON pv.vademecum_id = v.id
WHERE p.nombre = 'Juan';

-- Select nombre for Aspirina
SELECT p.nombre
FROM pacientes p
JOIN pacientes_vademecum pv ON p.id = pv.pacientes_id
JOIN vademecum v ON pv.vademecum_id = v.id
WHERE v.medicina = 'Aspirina';
2 Likes

(added to Tutorials section of FAQ)

- New version, add quantity of boxes.

  • In this version we can add the number of medicine boxes assigned to each patient, for example to Miguel: 2 boxes of Aspirina, 3 boxes of Ansiolitic and 1 box of Dolorin.

p55H_Sqlite_Farmacia_v2.aia (35.3 KB)

-- Create table intermediate pacientes_vademecum for relation many to many
CREATE TABLE IF NOT EXISTS  pacientes_vademecum (
  pacientes_id INTEGER,
  vademecum_id INTEGER,
  cantidad INTEGER,
  FOREIGN KEY (pacientes_id) REFERENCES pacientes(id),
  FOREIGN KEY (vademecum_id) REFERENCES vademecum(id),
  UNIQUE (pacientes_id, vademecum_id)
);

-- Insert relation pacientes - vademecum
INSERT INTO pacientes_vademecum (pacientes_id, vademecum_id, cantidad)
VALUES (
  (SELECT id FROM pacientes WHERE nombre = 'Juan'),
  (SELECT id FROM vademecum WHERE medicina = 'Aspirina'),
  3
);


-- Select medicina for Juan
SELECT v.medicina, pv.cantidad
FROM pacientes p
JOIN pacientes_vademecum pv ON p.id = pv.pacientes_id
JOIN vademecum v ON pv.vademecum_id = v.id
WHERE p.nombre = 'Juan';
4 Likes