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.
It can certainly be a good place to start. I used to rely on it pretty heavily. Another good way to do it is to look at the execution plan for your query. Often I have found the suggestions that it provides are incredibly useful. Another tool to help with indexing is to run this
SELECT
mid.statement
,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
This should give you an idea of some indexes you could be missing. Hope that helps.
Best Answer
Consider following the STOP pattern that's used in first aid training. Stop, think, observe and plan. That is, don't rush into action. Start by gathering some information, draft an action plan and only then start doing things. If there's an electricity accident, one doesn't touch the victim before cutting power. Likewise, one shouldn't start dropping indexes without knowing what use those would be.
Is the system working well enough? How can you tell? That is, what's the definition of "well enough?"
Do you have a set of business rules like "Report X must be ready within Y minutes" and "Daily ETL must be finished by 0600 as cube update starts at 0610." Maybe there's something like "Database backup must be finished at 0200, as disk-to-tape backup starts at 0300." Be aware there might be some ad-hoc rules like "CFO needs $stuff NOW" that are usually not documented at all.
If you don't have any business constraints, you need to discuss with your boss, other users and business about such constraints.
If you've got a set of rules, that's great news. Now, see if the system respects those limits at the moment.
After you know the rules for the system, maybe it would be a great idea to do some basic health check. Brent Ozar's sp_Blitz is widely used free tool. There's warnings if backups are not handled properly, lack of recent integrity checks, configuration issues and a lot more.