About database schema generator
Write your tables in a simple outline and get SQL with primary keys, foreign keys and relationships.
Relationships
- One-to-many: put a foreign key on the 'many' side.
- Many-to-many: add a join table with two foreign keys.
- Use foreign keys so the database protects your data from orphan rows.
Back up before you run SQL
Review generated statements, run them on a test database first and keep a backup. UPDATE and DELETE without a WHERE clause affect every row. Values pasted into SQL by hand can also open the door to SQL injection, so use parameterised queries in application code.
A worked example
An outline with users (id, name), orders (id, user_id, total) and a link from orders.user_id to users.id produces both CREATE TABLE statements and a foreign key. A product catalogue with a many-to-many link between products and categories needs a third join table with two foreign keys.
