Results 1 to 8 of 8

Thread: Animal Colours

  1. #1
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57

    Arrow Animal Colours

    Hi,

    It's late and I can't think straight.

    The problem:

    An animal has 1 or more colours (if not, at least a fixed 3 or 4 colours), and I need to be able to search for all animals who have the specified colours.

    i.e. All animals who are black and white (or white and black)

    Dog #1 is black
    Dog #2 is black, white and brown
    Dog #3 is white and black

    What would the SQL statement be to find Dog #3, for example?

    I should know this, but my mind is in a mess.

    I would think the tables would be animals, animal_colours, and colours, where colours is a 'lookup' table.

    Would really appreciate some help.

    TIA.

    _da.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    depends on what your db engine is but the "like" predicate may be worth investigating....

    eg
    select blah from <mytable> where <dogcolour> like "%BLACK%" AND <dogcolour> like "%WHITE%" order by <mysortorder>

    that will cater form a dogs colour that contains both words irrescpective of order, although you may need to consider whether you are suign upper or lower case for colours

    other dbengines use a different clause, buyt in prionciple they all have simalr functions

    you may need to use a different index mechanism (MySQL would use a 'FULLTEXT' index).

    undoubtably the 'smartest' solution would be to use a separatre table to store colours and that as a foreign key in the dog details.

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Hi Mark,

    It seems as if you're suggesting a single field for holding > 1 colour. I prefer to have normalized tables.

    Thanks.

    _da.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by darkangel
    Hi Mark,

    It seems as if you're suggesting a single field for holding > 1 colour. I prefer to have normalized tables.

    Thanks.

    _da.
    The animalcolours table would consist of, at a minimum, a field which is a foreign key to the animals table, AND a field which is a foreign key to the Colours table. It would also have a primary key consisting of BOTH the above fields. That is, the combination of Animal ID and Color ID would be unique and neither could be null. THis type of table, used to create a many-to-many relationship, is called an intersection table.

    Select FieldList from A, X, B Where A.PK = X.A_PK and B.PK = X.B_PK (Plus any other where clauses you need) In this example, A and B are the related tables, and X is the Intersection Table. X.A_PK and X.B_PK refer to the foreign key fields in the intersection table that relate the record to tables A and B, respectively.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by darkangel
    Hi Mark,

    It seems as if you're suggesting a single field for holding > 1 colour. I prefer to have normalized tables.

    Thanks.

    _da.
    actually I'm not
    your question suggested that you store your dogs colour as
    "Dog #1 is black
    Dog #2 is black, white and brown
    Dog #3 is white and black"
    the "like" suggestion was based on you storing your dogs colours in that format

    in fact I was suggesting you should store colours in a seaparate table and then use that as the FK in your dogs table. the only varaition is whether you see any relevance in storing information separately.
    do you see dogs that are black and white as a separate colour "scheme" to dogs that are black. ie could you foresee a requirement to specify "Black" as a search criteria and return all dogs which had a black colour (eg Black, Black & White, Black & Tan etc....) OR only dogs that are exclusively Black.

    if you have the requirement to return any dog which has black as a colour (ie Black, Black & White etc) then you need an interesction table. if you requiremnt is exclusive (ie if you specify black you only wnat to gewt dogs which are black, not a mix of colours includuing black) then a simple FK would work.

    as with all thingts its what you and your system requires, and also how much information you provide when you ask the question.....
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Hi loquin,

    I'm familiar with intersecton tables, that's what I mentioned above:

    I would think the tables would be animals, animal_colours, and colours, where colours is a 'lookup' table.
    The problem is that I'm not sure how to select all black and white animals, for example (without selecting black only or white only animals).

    Thanks.

    _da.

  7. #7
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Hi Mark,

    I'm sorry if I wasn't clear. That was just sample data, the model has yet to be created.

    Yes, I prefer to break it up as much as possible, to avoid redundancy.

    Good question -- I'll need to confirm with my client, but she did mention that she would like the query to return black-only and white-only animals, as well as animals having a combination of both colours, when doing a search for black and white animals. I guess I'd need to be able to offer both options, an exclusive search and a non-exclusive search. There is also a field in the old database (MS Access) named 'ColourPattern', with values such as 'Solid', 'Bi Colour', 'Tri Colour', and 'Striped'.

    Am I correct in saying that if I don't use an intersection table, the number of colours would have to be static? (included as columns in the animal table) I love the idea of a flexible design, where an animal can have any number of colours, but it's seems this can make selection of data a lot more complicated.

    Data modeling is so difficult.

    Thanks again.

    _da.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Its entirely up to you, and the custormer. The most elegant soluiton would be an intersection table as this should be significantly faster than using a full text search against colour names.

    -it also means that you can have as many colours as wished per dog.
    -it also means that you can identify any dog with Black as a colour
    -you would need to do a bit of SQL jiggery pokery to return dogs that are ONLY black though (ie ensuring that only dogs that have a single colour (black) are in the recordet and exclude those with more than just black).

    but it depends what the user requirement is and how many colour combinations for dogs there are. Some people may see just "n" basic colour or colour combination (eg if they are going by the Kennel Club specs) whereas others may see far more colours, especially if the dog is a street accident rather than a pedigree.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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