Relationships can be tough but, if you think them through, they may not be as complicated as you thought. I’m talking about database relationships, of course. If your site is using databases, you want to plan before you start coding. You want to keep thing that are related together, if possible, and things that are different separate. You probably want to keep usernames and passwords in the same table but not usernames and products in your online catalog.
Having a column that is unique for a table can be very helpful. It can allow you to identify a particular row of data. It also allows you to index your table by that column which can speed up table searches. It is commonplace to use a column entitled id or something like that and to auto-increment it for this purpose. This ensures that whenever you add a row of data to your table it has a unique identifier.
If you have a one to one relationship, this is very straight forward.
USERS username email fred firstname.lastname@example.org
What if you have multiple users and each one may make several orders? This is a one to many relationship. One could have a list of users. Since each order has only one user, the user id can be used to identify the purchaser of each order.
USERS userid username 1 fred 2 barney ORDERS orderid userid 1 2 2 1 3 2
In this case, barney placed the 1st and 3rd orders and fred place the 2nd.
What if you wanted to have a table that kept track of users favorite items in your product catalog. A user could have several favorite items and any item could have several users who like it. This is a many to many relationship and for this type of relationship you can use a look-up table.
USERS userid username 1 fred 2 barney PRODUCTS productid productname 1 bowling ball 2 hammock 3 sewing machine 4 brontosaurus ribs FAVORITES productid userid 1 1 1 2 2 1 2 2 4 1
Both fred and barney like bowling balls and hammocks. fred also likes the ribs but nobody likes the sewing machine. Poor sewing machine! As the order in which these favorites were entered is not relevant, there is no need for an index to the look-up table.
May all your relationship issues be solved as easily as this!