Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Link a general data table to multiple parent tables

    Hello,

    I need some design help. I'm trying to design a database for a dairy farm, I have a table for recording water use like

    Id
    StartTime
    EndTime
    Volume
    WaterUseType
    etc

    I also have two (or more) entities that use water, cowshed and paddock. I want to maintain a relationship between cowshed and wateruse, and a separate relationship between paddock and wateruse. Is it acceptable to have one column maintain a foreign key link to cowshed, and another column maintain a relationship to paddock. The design is allowed in sql server, but it doesn't really look right to me and I think would look even more silly if I added more water use entities e.g. farmhouse. Is there a term for this sort of concept, something to help me research the correct approach, or am I going about this the wrong way?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    whether the water is used in a paddock, a barn, a cowshed or whatever, its a location. So I'd suggest you have a table called location and use the PK of that table as the FK in your watering table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    76
    You could add a supertype called WaterUsers (Id PK) and change Id in Cowshed and Paddock to a foreign key referencing WaterUsers. Then, your WaterUse table needs only a foreign key reference to WaterUsers.

    If the subtypes are mutually exclusive and you want to enforce it, see Question on Structuring Product Attributes - comp.databases.theory | Google Groups for a discussion on how to nicely enforce exclusive subtype referential integrity without redundant subtype indicators and indexes.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by reaanb View Post
    If the subtypes are mutually exclusive and you want to enforce it, see Question on Structuring Product Attributes - comp.databases.theory | Google Groups for a discussion on how to nicely enforce exclusive subtype referential integrity without redundant subtype indicators and indexes.
    sheeeeeeit, man, that's tough reading

    i don't know if very many people are going to make it all the way through

    too much name-calling and one-upmanship getting in da way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by reaanb View Post
    If the subtypes are mutually exclusive and you want to enforce it, see Question on Structuring Product Attributes - comp.databases.theory | Google Groups for a discussion on how to nicely enforce exclusive subtype referential integrity without redundant subtype indicators and indexes.
    Any points raised by Derek Asirvadem can safely be ignored. Many feel the same way about Celko. Both of them have posted on dbforums and sqlteam before, and have done a pretty poor job of defending their methods.
    I'd say the biggest value of that link is illustrating the insanity to which people are willing to stoop in order to avoid having nulls in their databases. Allow nulls, and none of those designs are necessary or efficient. They all boil down to exercises in what I call "mssqlbation".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2012
    Posts
    76
    Quote Originally Posted by r937 View Post
    sheeeeeeit, man, that's tough reading

    i don't know if very many people are going to make it all the way through

    too much name-calling and one-upmanship getting in da way
    I agree. I didn't have a lot of time at work so I just linked the thread which I remembered when reading OP's post. Here are the parts that matter, without the egos:

    One way of implementing exclusive subtypes looks like this (continuing with iratonz's topic and my suggestion):

    Code:
    CREATE TABLE WaterUsers (
      Id INT NOT NULL PRIMARY KEY,
      UserType CHAR(3) NOT NULL
      CHECK (UserType IN ('COW', 'PAD')),
      UNIQUE (Id, UserType)
    )
    
    CREATE TABLE CowShed (
      Id INT NOT NULL PRIMARY KEY,
      UserType CHAR(3) NOT NULL
      CHECK (UserType = 'COW'),
      FOREIGN KEY (Id, UserType) REFERENCES WaterUsers (Id, UserType)
    )
    The disadvantage here is the duplicated UserType column, which adds no information to CowShed and is simply required for the composite foreign key reference. In addition, in deeper subtyping hierarchies, the duplicated type indicators add up like composite keys tend to do.

    The method described in the link is to write a function

    Code:
    CREATE FUNCTION WaterUserType
    (
    	@Id INT,
    	@UserType CHAR(3)
    )
    RETURNS tinyint
    AS
    BEGIN
    	RETURN COALESCE(
    		(SELECT 1
    		FROM WaterUsers
    		WHERE Id = @Id
    		AND UserType = @UserType)
    	, 0)
    END
    which makes the UNIQUE index in WaterUsers, as well as the UserType column in CowShed, redundant. Thus we have:

    Code:
    CREATE TABLE WaterUsers (
      Id INT NOT NULL PRIMARY KEY,
      UserType CHAR(3) NOT NULL
      CHECK (UserType IN ('COW', 'PAD'))
    )
    
    CREATE TABLE CowShed (
      Id INT NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES WaterUsers (Id)
      CHECK (dbo.WaterUserType(Id, 'COW')=1)
    )
    Quote Originally Posted by blindman View Post
    I'd say the biggest value of that link is illustrating the insanity to which people are willing to stoop in order to avoid having nulls in their databases. Allow nulls, and none of those designs are necessary or efficient.
    Heh, "insanity" is certainly descriptive of the ruckus that was often found on c.d.t.

    Disregarding those characters, however, do you have any thoughts on the method above or my suggestion to iratonz? I ask because it's a pattern I (infrequently) use in my own work.
    Last edited by reaanb; 04-01-12 at 05:27. Reason: Set primary key on final CowShed table

  7. #7
    Join Date
    Mar 2012
    Posts
    2
    thanks reaanb, I will have a go at your suggestion

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I'd say there are four methods you can use, and the choice depends upon the number of subtypes and the similarity of the subtypes.
    1). Use subtype tables as you have shown above. This is appropriate if there are just a few subtypes, and they are significantly different from one another.
    2). Use a single table and allow nulls. This is appropriate if there are a small to moderate number of subtypes consisting of mostly common attributes.
    3). Use a single table of common fields, with an XML column to hold values distinct to each subtype. This is appropriate for a large number of defined subtypes.
    4.) Use an EAV (Entity Attribute Value) model. This is appropriate if you have a large number of subtypes that are not predefined. This should be your last resort.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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