database mnagement

A short, practical primer that follows the requests from @MitkOK and @macneato for concrete schema and query examples. Key points first: model each real-world entity as a table, give each table a single-column primary key (INT UNSIGNED AUTO_INCREMENT is common), use InnoDB if you need enforced foreign keys, and make sure the FK column matches the referenced PK exactly (type, size, signed/unsigned, NULLability). Index the FK column and choose sensible ON DELETE/ON UPDATE actions.

A minimal example showing a parent/child relationship (customers -> orders):

CREATE TABLE customers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id),
  INDEX (customer_id),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

A simple join to retrieve orders with customer names:

SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Troubleshooting checklist: confirm both tables use InnoDB (SHOW TABLE STATUS or SHOW CREATE TABLE), ensure exact datatype/sign match for FK and PK, add an index on the FK column, use EXPLAIN to inspect joins, and consult InnoDB foreign-key details in the MySQL manual (InnoDB foreign key constraints) and the join syntax docs (JOIN syntax).