Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    56

    Unanswered: Restrict use of ResourceName to one?

    MySQL 5.1.36

    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?

    Thanks,
    David

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices View Post
    Is there a way to restrict the use of each resourceNameId to one within the "audio", "video" . . . tables?
    yes, add a column called "tabletype" (or similar name) and declare a UNIQUE index on the combination of (tabletype,resourceName)

    or you could ditch the useless auto_increment, and make (tabletype,resourceName) the primary key instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    56
    Thanks Rudy,
    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.?

    David
    Last edited by vivoices; 01-22-11 at 14:41.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices View Post
    ... and added a column "resourceType" ENUM('audio','video','image','text'), like you suggested
    hold on thar, baba louie

    , i never suggested ENUM, in fact if you google "ENUM is evil" i'm sure you will run across previous threads where i say just that

    Quote Originally Posted by vivoices View Post
    The 'audio', 'video', 'image', 'text' tables have a foreign key set to 'resource_names'.'resourceNameId' on their 'resourceNameId' column...
    hold on thar, baba louie

    you have separate tables for each of these different types? may i ask why not just one table?

    Quote Originally Posted by vivoices View Post
    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.?
    yes, if you really want separate tables, there is a way to do that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    56
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices View Post
    I do not see how to put this in one table and still have a normalized database structure.
    you could have a supertype/subtype structure

    the supertype table would have common columns like filePath, and presumably name and/or description as well, along with date added, added by, nomber of hits, etc.

    the subtype tables would have only those columns unique to that type

    see Data Based : Distributed Keys and Disjoint Subtypes for a method to enforce type codes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2009
    Posts
    56
    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.

    David
    Last edited by vivoices; 01-22-11 at 21:09.

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    this could be performed through triggers. From MySQL 5.5 SIGNAL's are available which allows you to raise an exception:

    Code:
    CREATE PROCEDURE p (divisor INT)
    BEGIN
      IF divisor = 0 THEN
        SIGNAL SQLSTATE '22012';
      END IF;
    END;
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •