Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2008
    Posts
    24

    Does this violate anything?

    junction table
    ------------
    car.....color
    1........1
    1........2
    1........3
    2........1
    3........2
    3........1

    if one car can be as much as 3 colors does the above junction/composite table violate anything is it the right way to approach it?
    Last edited by Datanalysis; 11-05-08 at 16:24.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    All kosher, but if you are LIMITING cars to a maximum of three colors, you will need additional constraints.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2008
    Posts
    24
    alrighty thanks, I might need to research these constraints up.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create table junction
    ( car integer
    , colour integer
    , seq tinyint
    , check ( seq in (1,2,3) )
    );

    not sure what the PK would be, though... blindman?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by r937
    create table junction
    ( car integer
    , colour integer
    , seq tinyint
    , check ( seq in (1,2,3) )
    );
    Why make every column nullable?

    At least you'll need a uniqueness constraint on (car,seq) otherwise it would still be possible to have more than three colours per car. It also seems sensible not to allow the same colour to appear twice per car. I'd expect referential constraints too:

    CREATE TABLE CarColours
    ( car INTEGER NOT NULL REFERENCES Cars (car)
    , colour INTEGER NOT NULL REFERENCES Colours (colour)
    , seq TINYINT NOT NULL
    , CHECK ( seq IN (1,2,3) )
    , UNIQUE (car, seq)
    , PRIMARY KEY (car, colour)
    );

    I'm not a fan of primary keys and I'm not stating an opinion about which key is the "primary" one or even whether it matters at all. Not to be too controversial though, I've included a PRIMARY KEY constraint anyway.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    You're not a fan of primary keys?

    I think you should go stand in the corner and think about what you just said for a moment.
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol! I think dportas might actually have some interesting points to make on this. I suspect his thinking is with regard to relational purity and (IIRC) Codd shoe horning PKs in as a late revision. There are a few that don't like the concept of PKs, saying they are just a hang over from, and concession to, technical standards that predate Codd's work.
    I very much doubt he is bemoaning constraints enforcing uniqueness.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    3 colours
    Seems a very small number of colour options for a car or has the US gone back to Henry Ford's original colour schemes for the model T? I know my old VW beetle comes in 17 colours for 1972 alone - my one is currently blue.

    , seq tinyint
    , check ( seq in (1,2,3) )
    Comment removed due to my misunderstanding.
    Last edited by mike_bike_kite; 11-06-08 at 07:53.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Mike - the requirement is 3 colour combinations per car.

    EDIT - more accurately, a combination of three colours per car....
    Last edited by pootle flump; 11-06-08 at 07:43.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    To clarify; 3 colours per car, not per model... right?
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Further clarification as I understand it:
    Each physical instance of a car (i.e. a tangible object that can be used to transport people and other matter from one place on earth to another, and not a theoretical construct such as a "model") can be coloured by up to three colours from a domain of available colours. I imagine each car must have at minimum one colour, otherwise it would be a magic car, but this has not been stipulated as part of the requirement.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by pootle flump
    I imagine each car must have at minimum one colour, otherwise it would be a magic car
    The DeLorean didn't have colours - it was just sheet steel - or does that count as a colour.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Magic?
    Or transparent!

    So we're talking about: car registration "UB3R FLMP" comes in yellow, red and blue. Yellow body, red dash and blue upholstery.

    In which case, is the colour not an attribute of the car part rather than the car itself?
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dportas
    Why make every column nullable?
    wanted to illustrate only the CHECK constraint in the seq column

    to include the FKs, and do a thorough job of it, would require that i ensure that the columns that the FKs reference also existed properly, which would mean i'd have to give the DDL for the cars and colours tables, too, and i couldn't be ***ed this time

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I wouldn't enforce this using a Sequence column.
    I'd enforce it using an indexed view, though that is an MSSQL specific solution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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