Relationships Can Be Tough

relationshipsRelationships 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                fred@slategravel.com

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!

About Robert

Robert is a novice web site developer. A lack of professional training has not prevented him from creating some very nice websites for small businesses who would otherwise have gone without a web presence.

Subscribe

Subscribe to our e-mail newsletter to receive updates.

No comments yet.

Leave a Reply