a database design best practices question

Results 1 to 2 of 2

Thread: a database design best practices question

  1. #1
    Join Date
    Dec 1969

    Default a database design best practices question

    So you have three tables. A ToolArea, ToolSet and Tool. a Toolset belongs to an area and a tool belongs to a tool set. <BR> <BR>So is it better to have a toolarea table with a AreaID and a ToolSet table with a SetID and a Tool table with a ToolID and a TOOLGROUP table that has as its primary key (toolid, setid, areaid) and it basically has a unique entry for every tool that exists. <BR> <BR>In this scenario to view a toolname and its corresponding area and set you would need to run this query:<BR> <BR>SELECT t.name, s.name, a.name<BR>FROM tool AS t, toolset AS s,toolarea AS a, toolgroup AS g<BR>WHERE t.toolid=1 and t.toolid=g.tid And s.setid=g.setid AND a.areaid = g.areaid;<BR><BR>VERSUS<BR> <BR>using foreign keys. So the tool table will have two more fields an areaID and a setID and there would be no TOOLGROUP table because the Tool table will have a foreign key relating its areaid field to that of the area table areaid field and its setid field to the set tables setid field. <BR> <BR>So the equivalent query as above would now look like:<BR> <BR>SELECT Tool.name,Toolset.name, ToolArea.name<BR>FROM Tool As Tool, ToolArea As ToolArea, ToolSet As ToolSet<BR>Where Tool.tid = 1 and ToolArea.aid = Tool.aid and ToolSet.sid = Tool.sid;<BR> <BR>I have to do this type of relationship alot. I am wondering if there are some best practices I should follow before things get too big and complex. Any thoughts?<BR> <BR>samir.

  2. #2
    Join Date
    Dec 1969

    Default I don't get it...

    ...why do you need the TOOLGROUP???<BR><BR>You wrote that you have:<BR> A ToolArea, ToolSet and Tool. <BR> a Toolset belongs to an area and a tool belongs to a toolset.<BR><BR>So isn&#039;t each Tool implicitly uniquely defined???<BR><BR>It&#039;s FK to the ToolSet implicitly defines the ToolArea that it also belongs to, no? What more is needed?<BR><BR>Are you trying to say that a tool might belong to MORE than one toolset? Or that a toolset might belong to MORE than on toolarea?<BR><BR>If so, then indeed you would need a many-to-many table for each level.<BR><BR>If you just have <BR> TABLE: ToolArea<BR> areaID : PK<BR> areaName : text<BR> TABLE: ToolSet<BR> setID : PK<BR> areaID : FK to ToolArea<BR> setName : text<BR> TABLE: Tool<BR> toolID : PK (not needed in this example, but assume for others)<BR> setID : FK to ToolSet<BR> toolName : text<BR><BR>Then you get full info by simply<BR> SELECT toolName, setName, areaName<BR> FORM Tool, ToolSet, ToolArea<BR> WHERE Tool.setID = ToolSet.setID<BR> AND ToolSet.areaID = ToolArea.areaID<BR><BR>I can&#039;t see any need for putting a FK to the ToolArea in the Tool. All it does is making enforcement of referential integrity harder.<BR><BR>

Posting Permissions

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