Database design

Firstly just to mention, this whole post will use web development for reference but the principles are applicable to all software using database designs.

When designing a database for a medium to large program / system it is best to keep an open mind with regards to future additional development on the system and design the database accordingly.

First we run through some of the table relational options.

1: one to one relation

2: one to many relation

3: many to many relation

One to one relation:

This is when you have one row of data in TABLE A that relates to one row of data in TABLE B.

This can be used when you have a user in TABLE A and the user's city TABLE B.

So in TABLE A you will have a field for the ID value of the user's city from TABLE B, thus one row of data in TABLE A is linked to one row of data in TABLE B

One to many relation:

One to many relation is when one row of data in TABLE A is linked to many rows of data in TABLE C.

For an example, we have a user represented by one row of data in TABLE A connected to multiple rows of data in TABLE C. In this case TABLE C can be orders a user has placed on the system. So one column in TABLE C will be for the value of the user ID from TABLE A.

Many to many relation

When working with a many to many relation in a database there is usually a 3rd "connecting" table involved .

So in this case, let's say we have a products table called TABLE C and a store location table called TABLE D and as mentioned there is a connecting table, that table we will call TABLE C_D.

The connection between the two tables will be added to TABLE C_D where you have the ID of the product in one column and the ID of the store location in another column. That allows you to have multiple products connected to multiple stores.

Those are the basics of database design and the structure that you always need to keep in mind when designing a database for a system.

When designing a database you will always need to keep the "Big Picture" of the system in mind to allow you to design accordingly. If you think a component of the system might have a chance of expanding into something bigger by request or by scope creep, you need to anticipate what changes might occur and plan accordingly when designing the database. That will save you allot of time in the long run when the changes needs to be made and you anticipated them instead of having to re-design some of the tables to accommodate the changes.