Related tables. SimpleSqlite extension. Pharmacy example

[Juan_Antonio]

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

[FREE] SimpleSqlite - an extension for working with SQLite Databases

This is a fork/rework/remix of Tom Bennedum's open-sourced aix-SQLite extension. (all Credits to Tom!) I have stripped it back to a bare-bones/expert version, without (hopefully) affecting functionality. The key to working with this extension/sqlite is: being clear about which database file and also which database you are working with remembering to OPEN the database to carry out data operations (SELECT etc) remembering to CLOSE the database to carry out file operations (IMPORT/EXPORT etc) t…

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

[Juan_Antonio]

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.

[Juan_Antonio]

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.

[Juan_Antonio]

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';

[ABG]

(added to Tutorials section of FAQ)

FAQ Section: Sqlite

Tutorials Related tables. SimpleSqlite extension. Pharmacy example - @Juan_Antonio Web SQL Database in Browser. JavaScript. HTML5 - @Juan_Antonio Extensions PuraVidaApps SQLite extension - Taifun Some SQLite samples - Peter Thomson Thomas Bennedum SQLite extension Pedroza SQLite extension - Thunkable HOWTO: php SQLite3 as server database with AI2 - TimAI2 HOWTO: Use sqlite3 with @Juan_Antonio’s Terminal Extension - TimAI2

[Juan_Antonio]

- 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';