Additional Database Design Concepts

Interesting Relationships

1. 1 to 1 relationships
   
   • Usually only done if you need to split a table into two
     • For security reasons
     • Limit Access to parts of the table
   • Both Tables will have a primary key
   • One table with have an additional Foreign Key that Points to the other tables PK

   • That Foreign Key will be set "UNIQUE KEY" additionally (so login_id in the users table should be UNIQUE)

     CREATE TABLE logins(
     login_id INT UNSIGNED NOT NULL PRIMARY KEY auto_increment,
     email VARCHAR(255),
     pwhash VARCHAR(255)
     ) engine=InnoDB Default Charset utf8mb4 Collate = utf8mb4_unicode_ci;
     
     CREATE TABLE users(
     user_id INT UNSIGNED PRIMARY KEY auto_increment,
     login_id INT UNSIGNED NOT NULL,
     fname VARCHAR(30),
     lname VARCHAR(40),
     UNIQUE KEY (login_id)
     ) engine=InnoDB Default Charset utf8mb4 Collate = utf8mb4_unicode_ci;
     
     ALTER TABLE users ADD FOREIGN KEY (login_id) REFERENCES logins(login_id);
     

2. Two FK's in one table pointing to a single PK in the other table
   

   • Have a "users" table
   • for orders, a user_id could be for the "customer" or the "salesperson"
   • This will require you to load the table twice with different aliases in the "FROM" the line

   • While this does eliminate the duplication from having a person in both a customers and salespeople table, queries will load the users table TWICE into memory, which may be the greater cost

     # FROM line to get both sales and customer information for the above
     # so "s" is the alias for salespeople so s.fname s.lname is the salesperson name
     # and c.fname c.lname would be the customer name
     
     FROM users c JOIN orders o ON (c.user_id = o.customer_id)
     JOIN users s  ON (s.user_id = o.salesperson_id)
     

   • You can use this for a hierarchical relationship (child/parent) type of thing

   • You can view "orders" as the join table between two tables "users s" and "users c"

   • For a site like Ebay, this is probably a good way to do it, as MANY users will be both sellers and buyers

     CREATE TABLE USERS(
     user_id INT UNSIGNED PRIMARY KEY auto_increment,
     fname VARCHAR(30),
     lname VARCHAR(40)
     ) engine=InnoDB DEFAULT Charset uft8mb4 Collate=utf8mb4_unicode_ci;
     
     CREATE TABLE orders(
     order_id INT UNSIGNED PRIMARY KEY auto_increment,
     customer_id INT UNSIGNED,
     salesperson_id INT UNSIGNED
     ) engine=InnoDB DEFAULT Charset utf8mb4  Collate=uft8mb4_unicode_ci;
     
     ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES users(user_id);
     ALTER TABLE orders ADD FOREIGN KEY (salesperson_id) REFERENCES users(user_id);
     

3. OR you can do it the normal way
   

   • This splits the Customers and Salespeople into two separate tables
   • as the customers table will probably be MUCH larger, the few duplicates you get will be offset by only having to load that large customers table ONCE
4. A One to Many relationship WITHIN a table
   
   • Like #3 you could split the employees and managers into separate tables, realizing that all managers are employees so you will probably duplicate a lot

   • Also done for hierarchical relationships

     CREATE TABLE employees(
     employee_id INT UNSIGNED PRIMARY KEY auto_increment,
     fname VARCHAR(30),
     lname VARCHAR(40),
     dob DATE,
     manager_id INT UNSIGNED
     ) engine=InnoDB DEFAULT Charset utf8mb4 collate=utf8mb4_unicode_ci;
     
     ALTER TABLE employees ADD FOREIGN KEY (manager_id) REFERENCES employees(employee_id);
     

Additional Resources

• Ten Common Database Mistakes
  • Note: The argument that you need a unique identifier that a human can understand is a good one, it does NOT mean you should use something like ISBN or TITLE for a PK, but you SHOULD use a UNIQUE KEY on a human readable field like that.
• 11 Important Database Designing Rules
• Database Design- Very Detailed MariaDB.com