Situation 1:
Your tables have one relationship and not two. (example: a Device
belongs to a DeviceType
)
So, keep only one relationship, the one with the composite keys (that include the Primary Key). The other relationship is redundant when the composite one is defined.
I would also suggest you have same names for related columns:
DeviceCategory Table
CategoryCode | Name | Description
----------------------------------------
WKS | Workstation | Description of what classifies an item as a workstation...
LPT | Laptop | Description of what classifies an item as a laptop...
DeviceType Table
DeviceTypeID | CategoryCode | Manufacturer | Model | IsTrackedInOtherSystemDefault
-----------------------------------------------------------------------------------
1 | WKS | Dell | GX1000 | true
2 | LPT | HP | dv4000 | false
3 | WKS | HP | xx9000 | false
Device Table
DeviceID | SerialNumber | DeviceTypeID | IsTrackedInOtherSystem | CategoryCode
------------------------------------------------------------------------------
1 | I81U812 | 1 | true | WKS
2 | N0S4A2 | 1 | false | WKS
3 | 3BL1NDMIC3 | 2 | false | LPT
So, the design would be:
DeviceCategory
--------------
CategoryCode PK
Name U1
Description
DeviceType
----------
DeviceTypeID PK U1
CategoryCode FK U1
Manufacturer U2
Model U2
IsTrackedInOtherSystemDefault
Device
------
DeviceID PK U1
SerialNumber U2
DeviceTypeID FK1
IsTrackedInOtherSystem
CategoryCode FK1 U1
and for the Computer
:
Computer
--------
DeviceID PK FK1
Hostname U1
IPAddress U2
CategoryCode FK1 CHK
The "additional" UNIQUE
keys (the two composite U1
ones) will be needed in most DBMS to enforce the foreign key constraints. I guess this answers your question 2, relationships needs indices to be enforced, so (you have to) use them. They will be used by the DBMS not only to enforce integrity but in your queries/statements, when you will be joining the tables.
The only one that is not needed is the U3
you had in the Computer
table.
About question 3 (the over-engineering part): No, I don't think so but that's just my opinion. And you haven't told us if this is a homework/exercise or a real project, whether you will be holding only your family's or a multi-million company's inventory, etc.
Situation 2
I think what you have is fine and there is no need (and not a good idea) to have referential integrity constraints on these columns. This is a default value that is copied in the second table via a stored procedure (I guess during Inserts on the second table?) or altered by a user. If you add an FK, won't that deny users the ability to override the default?
The names of the two columns are self-explanatory enough for a DBA to understand the functionality.
Well, it seems like all your data is meaningful, since you don't have a lot of it, and it all seems to have a role as a key or useful attribute.
If you have PK (by definition unique) on isbn in b_c, then this restricts a book to one class. Is that true? At that point you could argue the design that the class_id should simply then be an attribute of the book table and you don't even need the b_c table.
Since you already have a PK on b_c, I don't see the need for a surrogate key. Even if you were to expand to compound primary key on isbn, class_id to be able to allow multiple classes for a book, I don't really see a need for an additional surrogate unique key. In any case, it would only be an alternative unique key, I probably wouldn't make it the primary key and probably wouldn't use it in joins (joining to link tables is not terribly common as a foreign key, since they are usually identified by their parent or child as being part of a collection based on that relationship)
This is what I would do:
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`book_id` INT(10) NOT NULL AUTO_INCREMENT,
`isbn` VARCHAR(255) NOT NULL,
`title` VARCHAR(255) NULL DEFAULT NULL,
PRIMARY KEY (`book_id`)
) COMMENT 'Books used at this school';
/*
Also consider a unique constraint here on isbn
Also consider whether to allow NULL isbn when it isn't yet known
*/
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
`class_id` INT(10) NOT NULL AUTO_INCREMENT,
`teacher_id` SMALLINT(5) NULL DEFAULT NULL,
PRIMARY KEY (`class_id`)
) COMMENT 'Classes at the school';
DROP TABLE IF EXISTS `b_c`;
CREATE TABLE `b_c` (
`book_id` INT(10) NOT NULL,
`class_id` INT(10) NOT NULL,
PRIMARY KEY (`book_id`, `class_id`) -- note that book is no longer unique by itself
) COMMENT 'Books to classes';
ALTER TABLE `b_c` ADD FOREIGN KEY (book_id) REFERENCES `books` (`book_id`) ON UPDATE CASCADE;
ALTER TABLE `b_c` ADD FOREIGN KEY (class_id) REFERENCES `classes` (`class_id`) ON UPDATE CASCADE;
Best Answer
In your scenario, you have two 'entities':
Employees. These are stored in table
Employee Details
.Schedules. Stored in table
Schedules
. A schedule stores an Employee ID and other information.What you want to do is make sure that every schedule contains a valid Employee ID. That, you want to make it impossible to create a schedule with an Employee ID doesn't exist, and also to delete an employee from the
Employee Details
table if that Employee ID is currently being used in a schedule.These requirements are handled by what databases call a foreign key constraint. In Access, you can use a graphical tool ('Relationships') to 'link' the Employee ID column in the
Schedules
table to the Employee ID column in theEmployee Details
table so that the constraints I described above are always met.You can read up on foreign key constraints and Access' Relationships manager tool to accomplish this.