Professional Web Applications Themes

A Relational Model Question - Microsoft SQL / MS SQL Server

Jonathan Simms (ccoomm) writes:  A question that does not have a clear-cut answer. For the case as presented I might very well go for the suggested outline. But to always do like this would lead to a mess. And many database architects would probably perfer to duplicate the columns in each table. When it comes to modelling data, the object-oriented paradigm is probably superiour to the relational model. But modelling is not enough, and the relational model has proven to work very well when it comes deal with gigabytes and terabytes of data. What is important is that while O-O ...

  1. #1

    Default Re: A Relational Model Question

    Jonathan Simms (ccoomm) writes: 

    A question that does not have a clear-cut answer. For the case as presented
    I might very well go for the suggested outline. But to always do like this
    would lead to a mess. And many database architects would probably perfer
    to duplicate the columns in each table.

    When it comes to modelling data, the object-oriented paradigm is probably
    superiour to the relational model. But modelling is not enough, and the
    relational model has proven to work very well when it comes deal with
    gigabytes and terabytes of data.

    What is important is that while O-O thinking is not wrong when working
    with databases, one has to understand the limitations and accept
    compromises.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  2. #2

    Default re: A Relational Model Question

    Jonathan Simms (ccoomm) writes: 

    The LogEntry table should have a primary key too. (Customer_ID,
    DateTimeCreated) is a candidate.
     

    Yes. You probably want a clustered index on Customer_ID in the Logentry
    table. (Which you get if you use the suggested PK and don't make it
    non-clustered.)

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  3. #3

    Default Re: A Relational Model Question

    First off, thanks for your conscice and helpful answer. :-)

    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... 
    >
    > The LogEntry table should have a primary key too. (Customer_ID,
    > DateTimeCreated) is a candidate.[/ref]

    About the primary keys. Is it a Bad Idea to use GUID's (type
    uniqueidentifier) as
    primary keys? For instance, with your suggestion, could I make a column in
    the
    LogEntry table called LogEntry_GUID and then use that as the primary key?

    Does that violate the intended purpose of GUIDs?

     
    >
    > Yes. You probably want a clustered index on Customer_ID in the Logentry
    > table. (Which you get if you use the suggested PK and don't make it
    > non-clustered.)
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Jonathan Guest

  4. #4

    Default Re: A Relational Model Question

    Jonathan Simms (ccoomm) writes: 

    What's the intended purpose of GUIDs I don't know. I never use them. :-)

    For most cases, if you need an artificial key an integer column suffices.
    That's four bytes rather than 16.

    But try to use natural keys as long as such are easily found. For the
    LogEntry table, it seems that (Customer_ID, DateTimeCreated) is a good
    choice, unless you can get more than two entries within the same second.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  5. #5

    Default Re: A Relational Model Question

    >> When it comes to modelling data, the object-oriented paradigm is probably
    superiour to the relational model. <<

    And, why is that, Erland? OO 'Data' Model ( newly bottled old wine - same as
    the Network model ) is not a datamodel at all, it is informal, no
    theoretical basis and fails to conform to any fundamental properties of a
    sound datamodel. Relational model on the other hand has a stable and well
    founded basis, supported by reasoning & logic and relies on mathematical
    foundations. How, by any account, can the object-oriented paradigm, be
    superior?

    Implementations are not models and failures of implementation should not be
    attributed as drawbacks of the model.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  6. #6

    Default Re: A Relational Model Question

    >> I'm having a bit of trouble understanding the philosophical model
    used in SQL/RDBMS databases. <<

    That is a major conceptual leap. The conclusion that Bell Labs and the
    C++ boys came to was that OO is good for programming but not data, and
    vice versa for RDBMS.

    I have a "philosophical" book entitled DATA & DATABASES: CONCEPTS IN
    PRACTICE that might help.

    Objects have actions; relations simply exist. Objects are fairly
    independent of each other, floating about in some vague environment. A
    schema is a self-contained, complete little universe with its laws of
    nature. A row represents either a relationship or an entity, not both.
    Etc.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  7. #7

    Default Re: A Relational Model Question

    >> The main part where the relational model has proven its strength is
    speed. <<

    Unh? IMS and the network DBMS are much faster than most of the SQL
    products. I'd say the strength is the flexibility of RDBMS and the data
    integrity. SQL isd also more portable than IMS and the old network DBMS
    products.
     [/ref]
    up. <<

    Cache and several of the OO-DBMS products are quite fast. They make a
    point of advertising it as their major virtue.
     [/ref]
    compromises every now and then. Subtyping is a classic example. <<

    I am not sure what "perfect" would be, but if you don't use an OO type
    hierarchy for your modeling, you will not miss it very much.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  8. Moderated Post

    Default Re: A Relational Model Question

    Removed by Administrator
    Anith Guest
    Moderated Post

  9. #9

    Default Re: A Relational Model Question

    I'd use a Petri net for a real-time system. They require a lot of
    temporal stuff that is hard to capture and verify in a set-oriented
    model. See Rick Snodgrass's books on temporal data in SQL and anything
    on temporal logics.

    I'd use a textbase (doent management system) for pure text, such as
    legal doents -- semantic searches do not fit into a predicate logic
    very well. GIS for geographical data and CAD for blueprints because
    spatial data does not fit into a predicate logic very well.

    SQL and the relational model do a great job on structured data (that is
    what the 'S' in 'SQL' originally meant), but there are other kinds of
    data.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  10. #10

    Default Re: A Relational Model Question

    >> I'd use a Petri net for a real-time system.<<

    Even at the fundamental level, the Petri net assertions are nothing but
    logical predicates. As with any design structures, the Petri-net matrix
    predicates can be defined with attributes over a set of values and thus can
    be defined as a relation. Obviously, SQL based predicates have difficulties
    in fully asserting temporal information. However that does not mean that it
    is the relational model cannot handle temporal information. I think Date's
    most recent book has covered some aspect of it and has given a set of
    alternatives.
     [/ref]
    predicate logic very well. <<

    When the data within the (legal) doents are not required by the business
    to be represented as predicates, one can opt for a predicate-less system
    like text base & doc services. However that is more of a pragmatic decision.
    If the data within the doents becomes valid propositions for the
    business, (like say names, policy numbers, ssn etc ) then obviously one has
    to structure the data and relational representation provides the greatest
    benefit due to its scientific basis.
     [/ref]
    what the 'S' in 'SQL' originally meant), but there are other kinds of data.
    <<

    Data has to be structured to be useful. Yes, there are other kinds of data,
    but it is the structured representation of the data that makes it useful and
    more practical. As mentioned, representation of data is more of a practical
    decision based on how the business predicates are defined. Also there is
    this confusion in the database community to believe SQL representations are
    purely relational. The relational model simply defines the rules and
    semantics for structured logical representation. SQL being the popular query
    language, relational representations are forced to conform to SQL
    definitions. That does not mean that such data cannot be represented a set
    of propositions.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  11. #11

    Default Re: A Relational Model Question

    On Sun, 20 Jul 2003 21:23:07 GMT, "Jonathan Simms"
    <ccoomm> wrote: 

    There are books ...
     

    Yes, that would be a good start.
     

    Well, maybe. Lots of kids do it that way today. Back in the day,
    identit-integer fields were considered crufty. Semantically, I think
    that any ContactInfo is going to be associated with a supplier or
    shipper or customer, so I might seek to put one of those PKs into a
    more significant PK for the ContactInfo, rather than just an integer.
    OTOH, if one ContactInfo might apply to a supplier AND a shipper who
    is ALSO a customer, that won't work ... and you'd be right back to the
    integer. So, go with the integer, with a small sigh.
     

    Righto.
     

    Right. A "join" connects the stuff dynamically. A "view" stores away
    the join information conveniently for later use, rather than
    respecifying it in different SQL statements.
     

    Well, it's called "relations" rather than aggregation, y'know? :)

    You may have heard, that there is something in the relational model
    called "normalization". This is a fairly mechanical process you the
    designer go through to decide what goes in which table and what
    appropriate keys are. This is roughly the same process as defining OO
    classes, but the relational process is more formal and rigorous (and
    can actually be used to design OO classes, as well!).

    So, then you have tables. OK, you fill them with data. This is sort
    of like instantiating OO classes, only with more permanence, right?
    So, what you have then is about half of your aggregation done --
    static definitions, and data loadings.

    So, when do you do the other half of the aggregation? At run time.
    Wait a minute, let's go back. In OO, you might have methods to call
    to load up your aggregations. In relational, you write your SQL,
    views, and stored procedures to hold your business logic. OK,
    finally, in OO and SQL equally, at runtime you finish the aggregation.
    In OO you might hold the collection for later use, in relational you
    tend to build a collection, use it, then discard it quickly. Why?
    Because the database design itself has half (or more) of the
    aggregation permanently available. But that's style -- some people
    will dynamically build a temporary table (collection) and keep it for
    the duration of an application run. More commonly, if there's a place
    for somtehing like that, these days it is done as OO in some middle
    tier (um, noting that there is very seldom *really* a need for such a
    thing at all, if you have a good handle on using relational!)
     

    I encourage you to spend a lot of time researching the basics of
    relational database theory and practice. You may just be amazed at
    stuff that was worked out thirty years ago, that most OO projects hack
    or reinvent.

    Joshua Stern


    JXStern Guest

  12. #12

    Default Re: A Relational Model Question

    Anith Sen (com) writes: 

    I think you should be careful of referring to OO as "cooked up" and
    informal. There have been just as much thinking put into the
    object-oriented model as there have been into the relational model.

    You may not like it - but the way are stating your position you mainly
    come across as ignorant.
     [/ref]
    > for a real-time operating system. <<
    >
    > Why not? What is it about the information dealing with internal
    > structure of a 'real-time' operating system that makes it impossible to
    > capture?[/ref]

    As far as I know there are no languages that uses the relational model
    that are suitable for a real-time operating system.

    It can of course be disputed whether any truly object-oriented languages
    are suitable for the task as well, as true object-orientation requires
    garbage collection, and this is not really what you want in a real-time
    OS.
    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  13. #13

    Default Re: A Relational Model Question

    >> I think you should be careful of referring to OO as "cooked up" and
    informal. There have been just as much thinking put into the object-oriented
    model as there have been into the relational model. <<

    You are making apples out of oranges. You seemed to be placing the blame on
    me as if I was referring to object orientation as 'cooked-up'. I have not
    talked about Object orientation or OO languages or its principles. No doubt,
    object orientation is a great programming methodology. But, I was referring
    to OO Data models. And to make it clear to you, data models are the topic we
    are discussing. And of course, side tracking a discussion about data models
    into an argument of object-orientation vs. something else comes across as
    ignorance too.

    A data model has to precise and formal and cannot be cooked up as one wish.
    It must have definitive structural, manipulative and integrity components.
    If it helps, please read :
    - http://www.pgro.uk7.net/cjd3a.htm
    - http://www.pgro.uk7.net/fp6a.htm
    - Date's Relational Writing Series 94-97(ISBN 0201398141)
    - Also, the 3rd Manifesto by Darwen & Date has an appendix which details the
    confusion among object types, object classes etc. which makes the data
    models based on them fuzzy & imprecise.

    And obviously superiority of a Data model is not determined by the 'speed'
    of execution by a specific implementation of a model.
     [/ref]
    that are suitable for a real-time operating system. <<

    Let me repeat, what is it about the information dealing with a real-time
    operating system that makes it impossible to capture as a set of
    propositions?

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  14. #14

    Default Re: A Relational Model Question

    Anith Sen (com) writes: [/ref]
    > that are suitable for a real-time operating system. <<
    >
    > Let me repeat, what is it about the information dealing with a real-time
    > operating system that makes it impossible to capture as a set of
    > propositions?[/ref]

    You appear to be talking about data models from a theoretical standpoint.
    This might be of interest to an academic. To me all that matters is what
    is possible for me to implement a solution for my customers. Whether you can
    actually decribe a real-time operating system with a set of table is for me
    completely irrelevant, as long as this description cannot be used for an
    implementation.

    Let me note that since I started this discussion by saying O-O was
    better to describe data, that I "know" what I talk about. That is,
    I know what I was thinking on.

    The relational model has its advantages but also its drawbacks. For
    instance tables are squared, but real data is often jagged, which is
    addressed in the O-O world by inheritance. To do this in a relational
    world you have to do compromises.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  15. #15

    Default Re: A Relational Model Question

    Anith Sen (com) writes: 

    In the part that I have snipped you go ahead and point various places
    where SQL deviates from the relational model. When you go on saying that
    the relational model is superiour to everything else. That's a mouthful.

    First, the tool we have hand is SQL. So the model should fit with SQL.
    If it does not, that's a deficiency in the model. Of course OO fits
    even worse with SQL, because SQL is not object-oriented.

    Next, why do we model data? We do so, because we need to implement solve
    a business problem. The solution involves both data and actions with
    the data. Here is an advantage with OO: you can have actions with the
    data. Another advantange is that OO gives you a hierachy of inheritance,
    this makes it possible to illustrate how concepts are related.

    If I have a class Instruments, and a sub-class Options and another
    class Accounts, a reader can directly see that Accounts is a different
    concept. In table diagram which has some sort of relation to the
    relational model, the reader needs to scrutize the diagram a lot
    closer to see that the table Options is a sub-table to Instruments.

    This is why I feel that the OO model is better to describe your data.

    Your argument, as I can make out, that the relational model is superiour
    (note that I don't use this word myself), is that it is based on
    mathematics. But I find this a weak argument. And it gets even weaker
    when you say that SQL does not adhere to this model. Previously I
    pointed out that you don't use the relational model to describe a
    real-time OS. Now it turns out that people normally do not even use
    the relational model to describe data in a relational database!

    In another posting you say: 

    But in a lot of real-world problems physical ordering is part of the
    business problem. You asked Steve what this means:

    CustomerID ShipVia
    ---------- -----------
    ALFKI 1
    ALFKI 1
    ALFKI 1
    ALFKI 1
    ALFKI 2
    ALFKI 3
    ANATR 1
    ANATR 3
    ANATR 3
    ANATR 3

    But what if someone has been sitting by the roadside and taking notes,
    and this is the data we get? The data does not comply with the relational
    model it seems - but if the model cannot describe real-world data, it
    has a problem in my book.

    And this is even more apparent in output. Users are not very keen at all
    to view their data as unordered sets or bags.

    As for the issue of keys. Most tables in my database have a primary key,
    either a natural or an artificial. (A composed key that has has a foreign
    key which is artifical in its home table, counts as natural). But if
    you check all our SP code, you will find that most temp table does not
    have any keys declared. Many of them probably have a key, if you make
    the effort to find them, but I would not be surprised if there are
    temp tables that could have completely duplicate rows. This may be
    because they are the last step in the output to report or something,
    and in the output the users have no interest to see any key.

    Of course, you can lay a different perspective of things. But for me,
    and for most developers in this forum, the perspective is to solve
    business problems.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. Cheap 3D and a model question?
    By Applied CD in forum Macromedia Director 3D
    Replies: 2
    Last Post: May 24th, 02:24 AM
  2. Question: Model follows Camera
    By alex in forum Macromedia Director 3D
    Replies: 0
    Last Post: November 12th, 08:33 AM
  3. 3d model translate question
    By xxt in forum Macromedia Director 3D
    Replies: 2
    Last Post: October 11th, 01:04 PM
  4. question: culling model to view frustum
    By H.C.H. in forum Macromedia Director 3D
    Replies: 1
    Last Post: September 18th, 03:01 PM
  5. ASP.NET Model-View-Controller design question
    By Lewis Wang [MSFT] in forum ASP.NET General
    Replies: 1
    Last Post: July 21st, 03:54 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139