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.

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

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.

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

Hi loquin,

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

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.

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.

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.