Professional Web Applications Themes

Hey Experts! PeoplePlacesThings - Microsoft SQL / MS SQL Server

The following 2 articles are probably worth a read, as they explain super/subtyping (which is the "official" name of the concept we are talking about) in more detail: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag03/ html/HowManysTooMany.asp http://www.sqlmag.com/ InstantDoc #5226 Super/subtyping is not very useful if the the entities you're dealing with are very different. In datamodelling relations between entities are often expressed with a verb. In your example that could be: people travel to places, or maybe people were born in places, and people own things or people use things. You can't really have one verb to describe the relation between people on one side and ...

  1. #1

    Default Re: Hey Experts! PeoplePlacesThings

    The following 2 articles are probably worth a read, as they explain
    super/subtyping (which is the "official" name of the concept we are talking
    about) in more detail:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag03/
    html/HowManysTooMany.asp
    http://www.sqlmag.com/ InstantDoc #5226

    Super/subtyping is not very useful if the the entities you're dealing with
    are very different. In datamodelling relations between entities are often
    expressed with a verb. In your example that could be: people travel to
    places, or maybe people were born in places, and people own things or people
    use things. You can't really have one verb to describe the relation between
    people on one side and places and things on the other side, which is a sign
    that it is not a good idea to combine places and things.

    hth

    Jacco



    "me" <XcXoXmX> wrote in message
    news:mnt%a.173448$bloor.is.net.cable.rogers.com... 
    both 
    grouping 
    data 
    and 
    recordsets 
    look 
    object_id(N'[dbo].[aPeople]') 
    object_id(N'[dbo].[aPlaces]') 
    object_id(N'[dbo].[aThings]') 


    Jacco Guest

  2. #2

    Default Re: Hey Experts! PeoplePlacesThings

    >> Here's the problem, what if the things you are summarizing are very
    different. <<

    Then measurement theory tells us that you cannot do it. A summary or
    aggregation has to be on a set of like things measured on the same
    scale.
     [/ref]
    [sic] and they're all different? <<

    Look up the difference between a "field" and a "column" or attribute in
    an RDBMS.
     [/ref]
    summarize both for a printed report? <<

    That premise is absurd. A person is an entity; a place is an entity; a
    thing is an entity. Entities have attributes, not entities.

    1) You data element names are wrong – they tell us HOW the data is
    PHYSICALLY stored; you need to tell us WHAT they mean in the data model.
    That is one of many differences in a field and a column.

    2) You have no keys (IDENTITY can never be a key). To be something is to
    be something in particular; there is no such thing as the “mysterious,
    universal identifier number thingie”, and if there was, you would not
    put its data type in the name – in short, that “intid” is totally wrong.

    3) There are too many NULL-able columns, so this thing cannot hold much
    data, can it?

    4) I see you use the "Newbie‘s magic VARCHAR(50) NULL, one size fits all
    things in the universe" domain everywhere on the columns that might have
    been meant to be keys. The USPS standards for an address label is
    CHAR(35), so let’s use that for a person’s name (if you have a longer
    name, follow the USPS abbreviation for it).

    Let’s try to clean up the sample schema, ignoring the need for actual
    research into data types, check digits, constraints, etc. and just use
    common sense and a *lot* of assumptions

    CREATE TABLE People
    (person_id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(35) NOT NULL);

    CREATE TABLE Places
    (place_id INTEGER NOT NULL PRIMARY KEY,
    place_name VARCHAR(35) NOT NULL);

    CREATE TABLE Things
    (thing_id INTEGER NOT NULL PRIMARY KEY,
    thing_name VARCHAR(25) NOT NULL);

    A table models either a relationship or an entity. Your three-way
    relationship needs its own table, something like this:

    CREATE TABLE Events
    (person_id INTEGER NOT NULL
    REFERENCES People(person_id)
    ON UPDATE CASCADE,
    place_id INTEGER NOT NULL,
    REFERENCES Places(place_id)
    ON UPDATE CASCADE,
    thing_id INTEGER NOT NULL
    REFERENCES Things(thing_id)
    ON UPDATE CASCADE,
    ...
    PRIMARY KEY (person_id, place_id, thing_id));

    Now you can play with UNIQUE constraints on the person_id, place_id and
    thing_id columns to enforce various rules. You can add a time stamp for
    the event, or other attributes that belong to the relationship as a
    whole. For example, if this was an auction, where person bought thing,
    you might have the price paid, etc.

    You seem to be missing a lot of the basics of measurement theory, data
    model and RDBMS. Reports are done in a report writer or application
    program; SQL is a data retrieval language and nothing else.

    --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

  3. #3

    Default Re: Hey Experts! PeoplePlacesThings

    Joe,

    I appreciate the input but I find that your tone is a bit unconstructive.
    You will discourage others from replying with their opinions.

    This isn't a real database, I translated another busines problem into
    something more generic so people could discuss it easier. I just put
    varchars to get the point across. I don't think it hurts and it isn't the
    area that I'm asking for ideas on..

    Like it or not, you get requests for computer systems to prints stuff that
    involves different things on the same page. Many reporting systems work with
    a single recordset (except where sub reports are available).

    In a web system, you often use a URL to store the state of a system and I
    find that identity fields work very well for this.

    Examples:
    MyEditPage.asp?Action=Edit&id=25
    MyViewPage.asp?Action=Search&String=dogs+cats

    Are you proposing that we use:
    MyEditPage.asp?Action=Edit&id=Joe

    What if people have the same name?

    In your DDL, you changed the fact that a person can have some number of
    things and some number of places. I could have used dogs and cats perhaps.

    Darcy


    "Joe Celko" <edu> wrote in message
    news:#phx.gbl... [/ref]
    > different. <<
    >
    > Then measurement theory tells us that you cannot do it. A summary or
    > aggregation has to be on a set of like things measured on the same
    > scale.
    > [/ref]
    > [sic] and they're all different? <<
    >
    > Look up the difference between a "field" and a "column" or attribute in
    > an RDBMS.
    > [/ref]
    > summarize both for a printed report? <<
    >
    > That premise is absurd. A person is an entity; a place is an entity; a
    > thing is an entity. Entities have attributes, not entities.
    >
    > 1) You data element names are wrong - they tell us HOW the data is
    > PHYSICALLY stored; you need to tell us WHAT they mean in the data model.
    > That is one of many differences in a field and a column.
    >
    > 2) You have no keys (IDENTITY can never be a key). To be something is to
    > be something in particular; there is no such thing as the "mysterious,
    > universal identifier number thingie", and if there was, you would not
    > put its data type in the name - in short, that "intid" is totally wrong.
    >
    > 3) There are too many NULL-able columns, so this thing cannot hold much
    > data, can it?
    >
    > 4) I see you use the "Newbie's magic VARCHAR(50) NULL, one size fits all
    > things in the universe" domain everywhere on the columns that might have
    > been meant to be keys. The USPS standards for an address label is
    > CHAR(35), so let's use that for a person's name (if you have a longer
    > name, follow the USPS abbreviation for it).
    >
    > Let's try to clean up the sample schema, ignoring the need for actual
    > research into data types, check digits, constraints, etc. and just use
    > common sense and a *lot* of assumptions
    >
    > CREATE TABLE People
    > (person_id INTEGER NOT NULL PRIMARY KEY,
    > name VARCHAR(35) NOT NULL);
    >
    > CREATE TABLE Places
    > (place_id INTEGER NOT NULL PRIMARY KEY,
    > place_name VARCHAR(35) NOT NULL);
    >
    > CREATE TABLE Things
    > (thing_id INTEGER NOT NULL PRIMARY KEY,
    > thing_name VARCHAR(25) NOT NULL);
    >
    > A table models either a relationship or an entity. Your three-way
    > relationship needs its own table, something like this:
    >
    > CREATE TABLE Events
    > (person_id INTEGER NOT NULL
    > REFERENCES People(person_id)
    > ON UPDATE CASCADE,
    > place_id INTEGER NOT NULL,
    > REFERENCES Places(place_id)
    > ON UPDATE CASCADE,
    > thing_id INTEGER NOT NULL
    > REFERENCES Things(thing_id)
    > ON UPDATE CASCADE,
    > ...
    > PRIMARY KEY (person_id, place_id, thing_id));
    >
    > Now you can play with UNIQUE constraints on the person_id, place_id and
    > thing_id columns to enforce various rules. You can add a time stamp for
    > the event, or other attributes that belong to the relationship as a
    > whole. For example, if this was an auction, where person bought thing,
    > you might have the price paid, etc.
    >
    > You seem to be missing a lot of the basics of measurement theory, data
    > model and RDBMS. Reports are done in a report writer or application
    > program; SQL is a data retrieval language and nothing else.
    >
    > --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![/ref]


    me Guest

  4. #4

    Default Re: Hey Experts! PeoplePlacesThings

    >> This isn't a real database, I translated another business problem
    into something more generic so people could discuss it easier. <<

    That actually is not a good idea; try to simplify the problem to fit in
    a posting, but do not change the nature of it. One guy let a thread go
    on before he announced that the real problem was with overlapping time
    period and not street addresses or whatever he had originally posted;
    everything was void at that point.
     [/ref]
    that involves different things on the same page. Many reporting systems
    work with a single recordset (except where sub reports are available).
    <<

    That is why you use a report writer or application program -- in a C/S
    architecure, the front end, NEVER the backend, handles dsiplay for the
    users.
     [/ref]
    of things and some number of places. I could have used dogs and cats
    perhaps. <<

    No, I did not. A thing must have a location in time and space; that is
    an event and you could cannot drop anythign form (person, place, thing)
    without destroying the event.

    You are completely changing the model now with dogs, cats and onwership.
    That would give a table for dog owners and a table for cat owners.

    --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

  5. #5

    Default Re: Hey Experts! PeoplePlacesThings

    If people can have places and things, then why can't they have cats and
    dogs?

    "Joe Celko" <edu> wrote in message
    news:phx.gbl... [/ref]
    > into something more generic so people could discuss it easier. <<
    >
    > That actually is not a good idea; try to simplify the problem to fit in
    > a posting, but do not change the nature of it. One guy let a thread go
    > on before he announced that the real problem was with overlapping time
    > period and not street addresses or whatever he had originally posted;
    > everything was void at that point.
    > [/ref]
    > that involves different things on the same page. Many reporting systems
    > work with a single recordset (except where sub reports are available).
    > <<
    >
    > That is why you use a report writer or application program -- in a C/S
    > architecure, the front end, NEVER the backend, handles dsiplay for the
    > users.
    > [/ref]
    > of things and some number of places. I could have used dogs and cats
    > perhaps. <<
    >
    > No, I did not. A thing must have a location in time and space; that is
    > an event and you could cannot drop anythign form (person, place, thing)
    > without destroying the event.
    >
    > You are completely changing the model now with dogs, cats and onwership.
    > That would give a table for dog owners and a table for cat owners.
    >
    > --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![/ref]


    me Guest

Similar Threads

  1. One for the Experts
    By Darryle in forum ASP.NET Data Grid Control
    Replies: 5
    Last Post: October 17th, 07:37 AM
  2. For the experts! Please help!
    By darkone168 in forum Macromedia Flash Sitedesign
    Replies: 3
    Last Post: November 17th, 03:31 AM
  3. Experts?
    By Atrax in forum ASP
    Replies: 0
    Last Post: August 29th, 12:42 AM
  4. Here's one for you ASP.NET experts...
    By Luther Miller in forum ASP.NET General
    Replies: 0
    Last Post: July 3rd, 03:20 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