What's the point of drawing database table relations? (one to many etc)?

Question

I'm doing a project that see's me show my database table relations, but really don't understand why it's necessary.

Does it mean, if one table connects to another there MUST be a foreign key between them?

Answer 1

The point of the diagram is that it helps you to put some sort of fixed structure to your ideas. Other people can then also use it to check what you intend to build.

Any connections between tables will suggest a link between these tables, but it doesn't have to translate into a foreign key being defined. Foreign keys are seen as being very important though, they help keep the integrity of the database.

Answer 2

An entity relationship diagram (ERD) is used to create the structure of the database and the one-to-many is to simulate what could happen in reality. The structure helps formulate in your brain if the information you want to get out from the system can be released from the system. For example: a furniture store. One customer can purchase more than one item. Therefore there needs to be a connection between the customer information and the furniture information. That way, the personnel in the store will know how many customers have orders and also know when the product runs out. A unique customer number would be assigned to every order.

A foreign key is a primary key drawn from the primary key of another table. Therefore a foreign-key must be present between them. That's the connection.

Back to the example: Customer#1010 would therefore have order numbers# 100,150,200 and 220 due to the connection between the customer table and the products table.

Answer 3

Yes, entity relationship diagrams help a person visualize where where the foreign key relationships are. Also, using database tools such as Microsoft Sql Management Studio, drawing an arrow between two tables in a "diagram" creates a "constraint" which enforces that there is a legal foreign key value present when changing the table (that has the foreign key).

Answer 4

So that you can make reports etc.. Each table should only contain data that is relevant. Your reprot, however will want to draw data from different tables. Hence you need the relationship.