This is more of a comment, but since this is a lengthy response that's worth mentioning, I'm posting it as an answer.
Your database looks sufficient for your needs. (Well, as best can be assumed from the diagram.) I assume that the UserAccount
table has a one-to-many relationship between the four tables Vehicle
, Book
, VideoGame
and Movie
(from here on, the V-B-V-M tables.) Next, I assume that the Asset
table has a one-to-one relationship with each link between the V-B-V-M table entries. With this information, I would recommend moving the UserAccount_id
from each V-B-V-M table, to the AssetTable
.
Next, it seems that Asset
is always associated with one of the four V-B-V-M tables. Considering this type of setup, I would recommend creating an additional table-- AssetType
.
+-----------+
| AssetType |
+-----------+
| id |
| typeDesc |
+-----------+
Next, update the Asset
table and each V-B-V-M tables to include a AssetType_ID
field. the AssetType
table should include one entry, for each type of asset. In this case, Vehicle
, Book
, Video Game
and Movie
, each with a unique description and ID.
You may want to do this for a couple reasons-- First, you can now query the Asset
table and discern what type of asset is stored in a specific record, without having to link the data to your four other V-B-V-M tables. This will speed up your querying of the data, when all of the information from your four tables isn't needed. Second, this also provides an easy means of linking in a textual description of the type of asset associated with the Asset
entry. Again, without having to link to the V-B-V-M tables to figure that out.
Last, you may want to consider handling the barcode data differently. If barcode is a simple look-up, you can add it to the asset table. However, if barcode values have to be unique, keeping it in a secondary table is necessary because you'd have to put a constraint on the database. You could do this if it was integrated in the Asset
table, however, your vehicle data would cause problems because vehicles do not have barcodes. To my knowledge most, if not all, major database engines won't allow you to define a unique field, that also allows null
values because, then, the data is no longer unique.
You might be inclined to think that you could combine the Barcode
field with the VIN
. Since VINs are unique and Barcodes are unique, and neither should be the same, why can't they be combined into one field on the Asset
table, which allow you to drop the additional Barcode
table. I wouldn't recommend this because I think it gives the data less "focus".
Yes, it is a form of unique object ID but barcodes are barcodes and VINs are VINs-- the concept behind both is completely different and consolidating the two can become confusing with time. Furthermore, if you expand your project in the future, it could cause collisions. Technically, I don't see a big problem with that (in your case) but I'd still highly recommend avoiding that option.
Also, I would also add the assetType_id
to the Barcode
table, for the same reasons you added it to the Asset
table.
Given the tables you have (I'm assuming this structure):
Products
--------
ID
Name
VendorID
MedicalCode
HelpText
--------
ID
Description
I'd suggest adding 2 new tables:
product_help_text
-----------------
product_id
help_Text_id
help_text_types
---------------
id
description
And changing HelpText
:
HelpText
--------
ID
Description
HelpText_type_id
So now, you could have some data like this:
Products
ID | Name | MedicalCode
-----------------------------
1 | red pills | 445631-XS3
2 | blue pill | 043-PPLE-A
HelpText
ID | TypeID | Description
--------------------------------------------
1 | 1 | Take 3 times / day w.water
2 | 3 | Verify patient's health card
3 | 2 | check for other drug reactions
helpText_Types
ID | Description
-----------------
1 | For the patient
2 | For the doctor
3 | for the pharmacist
product_help_text
product_id | help_text_id
---------------------------
1 | 1
1 | 3
2 | 1
2 | 2
product_help_text
tells you which text goes with which product, and you can use the help text types to control which text gets seen where (for patient, pharmacist, doctor, drug sales rep, etc...).
Best Answer
From a Programmer standpoint, I would say the thing we want most is consistent, well defined and implemented standards for how the data layer will be designed and built. I am willing to play the way you want in your sandbox, you just need to tell me what you want, and not change the rules all the time. It should be implemented the same for everyone, even superprogrammergod. If you make exceptions for him then you want me to support and change it but re-implement it the right way that doesn't work for me.
And please do not tell me not to do it that way and walk away. Work with me to show me what you want, and why your way is better. If I understand I will comply every time. When I don't get it then its harder to comply. I do not want to be a DBA. I love programming I do not want your job and if you are a good DBA then I will be your biggest fan.