1

I'm wanting to create a relational database with the tables that I currently have in PHPMYAdmin. But I don't know how to go about it? I currently Have 4 tables, One users, friends, messages,orders, and stores. I believe I should be using a Many to Many relationship because the user needs to be logged in to order, and such. Any Ideas on How I should set my table up? This is just me tinkering around and Learning. Also how would you get and update the tables if they are all linked together?

Main

Friends

Messages

ORDERS

Stores

Users

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Bigfella
  • 27
  • 8
  • 1
    Seems like it is already a relational database. The user id's seem to be relevant... Otherwise, there are many ways to go about doing what you want to do. – Ice76 Feb 28 '18 at 01:42
  • Oh, Ok. I thought they had to be linked some how in the PHPMYADMIN. @Ice76 – Bigfella Feb 28 '18 at 01:43
  • You can check if there are foreign key constraints, but if there arent, then the code would just join on a certain key. – Ice76 Feb 28 '18 at 01:45
  • No, no. If you require relations between tables, do them as foreign keys in the database. Do not fake foreign keys in your code. It's error prone if you forget to link one item, code bugs, code crashes and corrupts your data, ... – Nic3500 Feb 28 '18 at 03:00
  • Ahh ok. Thank You!! – Bigfella Feb 28 '18 at 03:46

1 Answers1

3

My answer is a bit general but should help you to come up with the solution that's right for you. That being said, feel free to reach out for advice.

1) DESIGNING A RELATIONAL DATABASE (i.e. schema design)

You currently have 5 tables:

  • friends
  • messaging
  • orders
  • stores
  • users

However you need to first understand your application/the data/the problem and draw out how they relate to each other. Draw them out as an ERD (entity relationship diagram). For example, how do friends and messaging relate to each other? One friend may create many messages (one to many), but one message can only be created by a single friend. So FRIEND and MESSAGE is a ONE to MANY relationship. The "messaging" table needs a userid matching to a friend, which it seems like it does. So basically you need to map this out to see if your tables are designed correctly.

If you find a many-to-many relationship, you need to divide the tables so that you have one-to-many relationships (assuming you want to normalize your schemas).

Once you have a design, you can see if it matches with the current design or if you need to alter the tables or simply create new ones.

If you create new tables, you can then (carefully) migrate your data over using SQL or a script.

2) MySQL - MyISAM vs InnoDB

https://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html

MyISAM versus InnoDB

If you want to define the "relationships" between the tables and use foreign keys, with foreign key contraints, then you can create new InnoDB tables (if you opt to create new tables or a new database), or you can convert your existing MyISAM tables to InnoDB. Once they are InnoDB, you can make each table "relate" to each other, as per your database design.

For example if a friend is one-to-many with message then the message table would contain a friend-userid which will be a foreign key that REFERENCES the friend table userid column. InnoDB tables will have more overhead (slower) but give you true relational database properties that you may need (depending on your application).

Personally I prefer InnoDB tables if I design a MySQL database, unless I am thinking about huge data and performance which is another (good) problem to get into down the line.

Hope this helps.

rishijd
  • 1,294
  • 4
  • 15
  • 32