locked
is there a better way to model and create these tables? RRS feed

  • Question

  • I am trying to create tables for a project and am stuck with one part of the table(s) column(s).

    Tables

    Rmaster ((pk)SystemId, (pk)FileTypeId, (pk)ToolId, HeadOpAdj, Over, Backup, Rot)

    NRmaster((pk)SystemId, (pk)FileTypeId, (pk)ToolId, XAdj, YAdj, Overshoot)

    NRUser((pk)SystemId, (pk)FileTypeId, (pk)ToolId, (pk)UserIndex, XAdj, YAdj, Overshoot)

    SystemToolBevel((pk)SystemId, (pk)FileTypeId, (pk)ToolId, (pk)BevelId, IB, IC, EB, EC, Rotation)

    Tool((pk)ToolId, Description, ToolCode)

    Bevel((pk)BevelId, Description)

    FileType((pk)FileTypeId, Description)

    I am not sure if I should break out the tables with SystemId, FileTypeId, ToolId, any suggestions?

    WB

    Wednesday, August 14, 2013 4:06 PM

Answers

  • As scott said, it is hard to guess based on what you have given.  However I will give you some general suggestions.

    I do not like compound PKs.  It looks like you have a lot of duplicate ((pk)SystemId, (pk)FileTypeId, (pk)ToolId).  I would create a table with an ID to hold those 3 IDs.

    Also, without any knowledge of your specifics, think about what "owns" an attribute.  Does a "Bevel" own IB,IC, EC or Rotation?  Try to move any common attributes you can up to the owner.

    Wednesday, August 14, 2013 5:48 PM
    Answerer
  • You also left out the foreign keys - which is an important part of this discussion.  It is difficult to discuss any schema when it is not clearly defined - that is the primary point to be made here. That is a common mistake and it is easy to do when you, the poster, are so intimately involved with the system.  Things that are obvious to you tend to be not so obvious to someone at a distance.  In addition, it is unfortunately commonplace for people who have very little knowledge of relational databases to be designing them and forgetting important things - like primary key, foreign keys, constraints, how to implement a synthetic key and still maintain correctness within an entity, etc.  I have no idea where you fall in the spectrum of relational knowledge - so if you leave something out what should I assume?

    Back to improvements.  I suggest you design based solely on natural keys first.  Do not assume that any table must (or should) have a primary key that is an "auto-number".  While it may prove useful during implementation, that decision can wait until the model is complete and accurate - then you optimize where needed.  In addition, you should carefully choose which index is the clustered index of every table and not simply leave that decision to some default logic - something that many do without realizing the importance of actively making that decision.  And, of course, whether a table should or should not be a heap is another actively made decision.

    As I mentioned (as does Tom), the presence of "tables with the same composite pk" is a concern.  How to address it depends on knowing what exactly you are modeling.  To simply add another table that consists of an identity column and move these 3 common columns into it (and adjusting the current tables accordingly) does not necessarily make the model "better" or more accurate.  One needs to know what those entities represent, how they are used, and how they are related to make that decision.  By making the suggested change, you have now created an enforced relationship between the tables that may not (or should not) exist.  That is why it is important, critical even, to understand the real life entities that are being modeled. A mistake at this point is difficult to catch and has far reaching consequences.

    • Marked as answer by Fanny Liu Tuesday, August 20, 2013 2:37 AM
    Wednesday, August 14, 2013 7:10 PM

All replies

  • Your schema is supposed to model something in real life.  Given the lack of information that describes the entities, their relationships, the intended usage, and what real entities these are supposed to model it is difficult for anyone to provide any really useful constructive input.  In addition, bolding and abbreviations are not a particularly useful substitute for actual DDL - you provided no indication of foreign keys but perhaps we should infer those based on common table names?

    To answer your actual question - it would help if you could define what "break out the tables" means.  Given the names and the composite primary keys, it is possible that there are other entities that you have not explicitly modeled (or either left out).  Usually, a column name that ends is "id" is the primary key of a table - yet you have no System table.  The column UserIndex has the same implication (and, if so, a lack of consistent naming I consider an issue). You have tables with the same composite primary key - this is a concern but not inherently wrong. 

    Given the names you have provided, it appears you might be working within a rather specialized area of knowledge.  If so, I suggest you treat any suggestions with caution since lack of familiarity with that area may lead to suggestions that are not appropriate.

    Wednesday, August 14, 2013 5:28 PM
  • I was trying to keep the details generic because the schema is rather specific to industry terminology.  Yes, I have left off some tables such as System and File.  Again, just to keep the issue specific to the tables I thought were relevant.  It appears I have failed to clearly give you the whole picture.

    I have tried to present the table structure without the DDL because I felt that was to much detail and given the relatively simplistic and generic table I presented was hoping it wouldn't cause an issue.  Your tone in response is noted.

    Breaking out the tables was meant to state, "Should another table with a primary key (auto number) be added with columns for SystemId, FileTypeId, and ToolId and then use the primary key in the other tables as a foreign key?

    Wednesday, August 14, 2013 5:42 PM
  • As scott said, it is hard to guess based on what you have given.  However I will give you some general suggestions.

    I do not like compound PKs.  It looks like you have a lot of duplicate ((pk)SystemId, (pk)FileTypeId, (pk)ToolId).  I would create a table with an ID to hold those 3 IDs.

    Also, without any knowledge of your specifics, think about what "owns" an attribute.  Does a "Bevel" own IB,IC, EC or Rotation?  Try to move any common attributes you can up to the owner.

    Wednesday, August 14, 2013 5:48 PM
    Answerer
  • Thanks, just looking for some input and you provided it...without attitude.

    I will go that direction.

    Wednesday, August 14, 2013 5:52 PM
  • You also left out the foreign keys - which is an important part of this discussion.  It is difficult to discuss any schema when it is not clearly defined - that is the primary point to be made here. That is a common mistake and it is easy to do when you, the poster, are so intimately involved with the system.  Things that are obvious to you tend to be not so obvious to someone at a distance.  In addition, it is unfortunately commonplace for people who have very little knowledge of relational databases to be designing them and forgetting important things - like primary key, foreign keys, constraints, how to implement a synthetic key and still maintain correctness within an entity, etc.  I have no idea where you fall in the spectrum of relational knowledge - so if you leave something out what should I assume?

    Back to improvements.  I suggest you design based solely on natural keys first.  Do not assume that any table must (or should) have a primary key that is an "auto-number".  While it may prove useful during implementation, that decision can wait until the model is complete and accurate - then you optimize where needed.  In addition, you should carefully choose which index is the clustered index of every table and not simply leave that decision to some default logic - something that many do without realizing the importance of actively making that decision.  And, of course, whether a table should or should not be a heap is another actively made decision.

    As I mentioned (as does Tom), the presence of "tables with the same composite pk" is a concern.  How to address it depends on knowing what exactly you are modeling.  To simply add another table that consists of an identity column and move these 3 common columns into it (and adjusting the current tables accordingly) does not necessarily make the model "better" or more accurate.  One needs to know what those entities represent, how they are used, and how they are related to make that decision.  By making the suggested change, you have now created an enforced relationship between the tables that may not (or should not) exist.  That is why it is important, critical even, to understand the real life entities that are being modeled. A mistake at this point is difficult to catch and has far reaching consequences.

    • Marked as answer by Fanny Liu Tuesday, August 20, 2013 2:37 AM
    Wednesday, August 14, 2013 7:10 PM
  • This is not a skeleton; it is cremation :) There is not enough meat left to help you and there are some basic data modeling errors. You have no idea what the ISO-11179 standards are. Let me list the obvious stuff and guesses to start discussion. 

    This looks like numeric tool database of some kinds. 

    The term, “master” was used in tape files and latter in network databases; it is not a Relational term. And what is an "R"? 

    “file_type_id” is absurd; you can have a “file_id” or “file_type” in a valid data model. What is your “blood_type_id”? NO! It is just a blood type. This is usually a noob who has created a look-up table and used an IDENTITY column for its key. That sucks. More often than not, the look-up should have been a “CHECK (col IN (..))” instead because the list short and static. 

    OVER is a reserved word, not a data element name. But it is also too vague to be useful, like overshot, backup (computer term?), rot (short for “rotation”  or “spoilage”?) 

    Without the DRI and DRI actions, the bones of your skeleton are disjointed. 

    Do you really have only one Tool, as the name says? Only one Bevel? But a  “bevel” is a shape, not an entity, so it makes no sense. 

    This is a start at what we need to help you. See the DRI? The corrections to ISO-11179 rules? I am not even trying to get datatypes or constraints yet. 

    CREATE TABLE R_Master-– needs valid name; what is an R?
    (system_id, 
     file_type 
        REFERENCES FileTypes (file_type)
        ON UPDATE CASCADE,
     tool_id, 
     PRIMARY KEY (system_id, file_type, tool_id),
     head_op_adj,
     over, -– needs valid name
     backup, -– needs valid name
     rot -– needs valid name, "<something>_spoilage"
    );

    -- the referenced table might not need to exist. Are there hundred of file types? Or do they change frequently? If so, do we need a lifetime (start_date, end_date) pair as they expire? 

    CREATE TABLE FileTypes
    (file_type, file_description)

    Can you do the rest? 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, August 14, 2013 8:36 PM