Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2008
    Posts
    3

    Wink Unanswered: declaring a composite attribute in sql server

    hi guys i have a question ..... i would like to know how to delare a compound attribute in this case would be Birthinfo onto a sql server script. from this diagram . thanyou guyt in advance and any suggestion or help would be greatly appreciated .....
    Attached Thumbnails Attached Thumbnails New Bitmap Image (2).bmp  

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No idea what a compound attribute is. Is it the same as a composite alternate key? If not - what is the definition of a compound attribute?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2008
    Posts
    3
    srry i had a typo it was "Composite Attribute"

  4. #4
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    You mean you want to add a single attribute to the table that actually consists out of three properties?

    So a single field that contains both country, birthdate and city?

    I don't think that exists, unless you create such an entity in your client/middle tier and store some serialized form within a column.

    You could make a detail table with these three items and a sequence field and then use a foreign key to that table but that is kinda clumbsy.

    Why don't you simply add all three fields to the table? Or do you have more then one of these combinations and you would like them logically grouped?
    Greetz,

    Bastiaan Olij

  5. #5
    Join Date
    Mar 2008
    Posts
    3
    so it would be just add those attributes to the Person table ??

  6. #6
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    lucho,

    In this case I think thats what I would do, don't really see a point to normalize it.

    I do have to admit it would be nice if you could do composite attributes or whatever you would want to call them. I have often longed for combining a foreign key to a currency table with an amount field as a single unit. As they work together it would make sence, and I often have tables with two or three amounts that could potentially all have a different currency.
    Greetz,

    Bastiaan Olij

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by BasOlij
    In this case I think thats what I would do, don't really see a point to normalize it.
    Adding three columns is normalised. How is it not normalised? As I am starting to understand it this composite attribute would actually not be normalised (violation of first normal form - not attomic).


    lucho - use three columns. If you really must, you could use XML.

    Actually - I've just looked at your diagram again. What's the deal with the Name entity?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    pootle,

    yeah you are right, I'm mixing up things, sorry bout that.

    Still the composite attribute, breaking the rules or not, does seem like a nice idea.

    You could indeed use XML to combine the three into one field, but it would making querying more difficult, and you wouldn't be able to put a referential constraint on those fields that require it (assuming that for instance the country is selected from a country table).
    Greetz,

    Bastiaan Olij

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by BasOlij
    Still the composite attribute, breaking the rules or not, does seem like a nice idea.
    I don't like it - what does it gain? Where do you stop? Composite address attributes? Composite name attributes?

    The day and place of birth are funtionally dependent on the PK, not each other. I don't get it. Maybe it is some theory thing I've not come across before...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you guys should see what the sql 2003 standard allows -- entire rows stored inside a single column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    pootle_flump,

    The examples you just mentioned, can't agree with you more. Its like going back to the dark ages...

    You are also absolutely right that choosing both country and city seems interesting, I can understand this choice from an interface point of view (first choose the country, then choose a city within that country), but it should be enough to just store the FK to the city, and in the city table store an FK to the country.
    That said, most systems I've come across only have a country table, city often is a freely enterable field.

    The only practical situation I can think of when it would be nice to combine two fields as one, or have them somehow logically grouped, is the situation I mentioned before of joining a currency FK and amount together. If only to clearly communicate that currency FK represents the currency for that amount field. Like I mentioned, I have tables with more then one amount in different currencies.
    Still I've survived just fine so far adding multiple currency FKs and multiple amount fields to my table and using some good names to visually identify which belongs to which.

    r937, do I really want to know?
    I've been mingling with storing XML data sets within columns in temporary tables but only because that turned out to be handy with importing/exporting hierarchical data. Not really sure if I would want to have such things in my day to day database tables
    Greetz,

    Bastiaan Olij

Posting Permissions

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