I use a table "resource_names" to make sure resouce names are unique throughout the application.
| resourceNameId | resourceName | resourceNameAssigned | resourceNameDescription |
| primary key | unique | | |
Resouce names are assiged in "audio", video", "images" and "texts" tables.
Is there a way to restrict the use of each resourceNameId to one within the "audio", "video" . . . tables?
A resource name has to be unique for the whole app.
So I left the UNIQUE constraint on "resourceName" in the table "resource_names" and added a column "resourceType" ENUM('audio','video','image','text'), like you suggested, just without including it to the UNIQUE constraint. So a resource name is still unique in the whole application.
The 'audio', 'video', 'image', 'text' tables have a foreign key set to 'resource_names'.'resourceNameId' on their 'resourceNameId' column and a UNIQUE constraint as well.
Now that should take care of UNIQUE resource names.
Is there a way to "filter" a foreign key to only the rows in 'resource_names' that have the appropriate 'resourceType' set?
That would prevent inserting a 'resourceNameId' of the wrong type into the 'audio', 'video' etc. tables.
In other words: Can I restrict the entry of 'resourceNameId's into the 'audio' table to the 'resourceNameId's that have 'audio' as the 'resourceType' etc.?
ENUM is evil . . . will read more about it.
I don't need to use ENUM.
The video type has titleTextId, thumbImageId, filePath, width, height, bytes and duration as defining columns.
Audio has filePath, bytes and duration columns.
Image has filePath, width, height and bytes.
Text has just a text column.
All types exist for a variable amount of locales.
I do not see how to put this in one table and still have a normalized database structure.
This is good advice Rudy, thanks.
Just too bad that MySQL (even 5.5) does not validate CHECK CONSTRAINTS and data is inserted or updated.
I have to figure out a workaround.
I thought about creating a table with one row only for each resource type e.g. "image_resource_type" and set a foreign key in the "images" table.
That should work, butt seems way too elaborate a design.
this could be performed through triggers. From MySQL 5.5 SIGNAL's are available which allows you to raise an exception:
CREATE PROCEDURE p (divisor INT)
IF divisor = 0 THEN
SIGNAL SQLSTATE '22012';
These were not available prior to 5.5 as a result we could never raise an exception inside a trigger. The code above is to just show how the signal could work i.e. very the inserted or updated entries and either commit to the database or raise an exception.