Entity relationship

Question

This question is a continuation to another question, also entitled Entity Relationship.

Dejene: you should transfer the material here into the original question and then delete this question.

---

Elaboration: Yes, 'Field ' is referring to areas of land. We have several cane growing fields at different location. It [each field?] is named and has budget.

User is not referring to individual who are working on the machine. They are departments. I used 'isDone' table to link userDept with machine. A machine can be used by several departments and many machines can work for a userDept.

A particular machine can be used for multiple tasks on a given shift. It can work for say 2 hours and can start another task on another field. We have three shifts per day, each of 8 hrs!

If I use Auto increment PK, do you think that other key are important? I don't prefer to use it!

Usually, I use auto increment key alone in a table. How can we create relationship that involves auto increment keys? Thank you for thoughtful comment!!

Dejene

Answer 1

Relations between tables ("foreign keys") are defined between columns, not values. Example:

create table machine ( id identity );
create table owner ( id identity );
create table isDone ( machineId, ownerId );

Now you can say "isDone.machineId must exist in the machine table":

ALTER TABLE isDone 
  ADD CONSTRAINT machineFK 
  FOREIGN KEY (machineId) 
  REFERENCES machine (id)
;

This has the effect that the database will return an error if you try to insert a row into isDone with a value for the column machineId which doesn't exist anywhere in the table machine (where select Count(*) from machine where id = machineId returns 0).

So you must separate the definition time (where you say what is valid and what is not) and the time when you manipulate the data in the database.

When you use this feature of your database, you must make sure that the machine and the owner exist at the time when you create a new row in isDone. This is usually simple: There are new shifts every day but no new machines and departments. So you can create new machines and departments when they are bought/founded. The first shift using one of them is probably going to happen a long time after that.

As for other keys: The auto-increment column should be the primary key (PK) of your table and nothing else. Later, you can create indexes on other column if you find that certain queries perform badly. But for starters, an auto-inc PK column should be enough. This makes it more simple to form foreign key constraints.

Example: If you would use the name of the department as the primary key, you could have problems when a department changes its name.

Also, I suggest that you use the real name of the objects (for example, "department" or "owner" instead of "user") in your design. This will make it more simple to see what is what and where to look when you search something.