ddlerd
I am currently establishing a database for Students and Courses using ERD. The style of ERD I am using is similar to the diagram below from the ERD Diagram Question:
Note: The picture above is NOT my ERD, this is just the style of ERD I am using.
A simple CREATE statment could be used for the attributes of each entity, but what other DDL statements enforce relationships, weak entities, and multi-way relationships?
The key to understand this case is at the ninth page from Doc-A:
"product" and "customer" are entity types, and "shipped to" is a relationship... the verb "ship" is then converted to a gerund... in other words, the relationship... has been converted into the entity type "shipping"
Hence, the final third form will have "shipped to" converted to an entity by itself, "shipping".
This is necessary because "Shipped to" is a MANY-TO-MANY relationship (although to myself Chen's text is not clear on this).
But, your example does not relate - the relationship "Identified by" is an ONE-TO-MANY (a card belongs to ONE customer / a customer have ONE OR MORE cards issued).
Hence, the model would have the relationship between "Bank" and "Bank card" identified by TWO verbs.
Or, making the model stronger, to register several authentication events, one may turn "Authenticated by" into a entity by itself, "Authenticating" (to register event date, data shared, etc.).
I think you'll want an EmployeeCanAuthorizeJob table and an EmployeeCanAdministrateJob table for a straight many-to-many representation ( right now, I think you'll find you'd be duplicating your job records with the current structure ). This way, you could tie many EmployeeIDs to JobIDs and vice versa for either of the management tasks.
That said, I would likely only end up creating an EmployeeCanManageJob table, along with possibly a secondary look-up table ManageJobType. The purpose of the second table would be to include the functions an employee can perform on a particular job. Today you've got two, but who knows if CanDelete of whatever needs to be broken out later down the line?
EmployeeCanManageJob
------------------------------------------------
PK | Employee_FK FOREIGN KEY Employee.EmployeeID
PK | Job_FK FOREIGN KEY Job.JobID
(PK | ManageJobType_FK FOREIGN KEY ManageJobType.ManageJobType_PK)
This way, if ManageJobType_PK 1 was "Administrate" and 2 was "Authorize", employee 1 could administrate job 1 with a ( 1, 1, 1 ) record and authorize the same job with a ( 1, 1, 2 ) record.
As per the edit:
There at least two popular methods of change tracking: the first being an audit table, the second being some form of slowly changing dimension. To save you a little grief down the line, you are not dealing so much with a "slowly changing dimension" here as you are a "rapidly changing fact," if you will, so I expect you will find yourself looking at one of the numerous ways to implement the audit table scenario. Which method you choose will largely be subject to you and your team's comfort level with the technologies involved ( or more specifically, each options advantages and disadvantages ). I've personally always been a fan of the log trigger approach and think it would be more than suitable for your needs here, but if you have out-of-the-box capabilities available to you, like in Sql Server, perhaps you and your team would be more comfortable with that method.
Best Answer
The way you can enforce relationships between tables is by creating a foreign key which is referencing a primary key from other table. Both keys are defined either creating or modifying the table by using CREATE statement or ALTER statement, respectively.