How to model differing criteria?

Question

I need to be able to match prospective owners to abandoned animals based on varying criteria and locations.

The owner will have a particular criteria set. Animal type = "dog", breed = "Labrador Retriever", age will need to be between 1 and 5, sex = male, and so on...

The animal will also have a particular criteria set. The animal type = "Dog", age = 3, sex = male, breed = "Chihuahua".

The animal could also be: type = "Cat", age = "12", sex = female, breed = "Tiger".

I also have a "Location" model for both the owner and the animal (polymorphic) that contains the information related to the location of either the animal or the owner.

So that part is easy...

The hard part (at least for me) is when I need to specify different criteria for different animal types. So an animal of type = "dog" may have a criteria of "can fetch?" whereas an animal of type cat may have a criteria of "de-clawed?" and a animal of type "fish" may have criteria of "pattern" with multiple options of ["speckled", "striped", "plain"].

What I have now is an "animal" model with the generic animal information (age, sex, breed), then I have a breeds model with the various breeds per animal type, but I can't figure out how to abstract out the criteria that differs between the animal types.

Again, this is just an analogy because I don't think my actual problem will make any sense to anyone else. What I need is just some pointers in the right direction, maybe a link or two. I just can't seem to work out how to make this happen in Rails without creating a separate table for each criteria set, as in dog_criteria, cat_criteria, fish_criteria, and so on...

Answer 1

Sometimes we resort to simple name/value pairs (or name/value/type triples ) for such properties. This saves adding new types (and database tables) for every animal species in the world. Or worse for each breed: Consider poodles ... they might have an extra field "shaved decoratively" ... and St Bernards, brandy capacity ...

Answer 2

In the software modeling world, this would obviously be done with a class for each species, I.E. "Dog", "Cat", "Fish" which extend "Animal". In the relational database world, this becomes a bit harder to represent.

If you wanted to match this object-oriented approach in your database, you would have a table for "Animals" and then a table for each species, "Cat", "Dog", and "Fish". Then you would probably have a species table (or a hard-coded enum in your code) that would give you a value to place in the Animal row for which species each animal was. This would tell you how to look up further information for each animal.

This is probably not the best approach. What you have is more what I would call "Custom Data" for each animal. You should define one table that has a list of custom attributes, and another table to match these attributes to a value for each animal row.

If you'd like to make it more convenient to see and control which attributes can apply to which species you could make a third table for "Categories" which would link to the Animal species and to a collection of attributes. Then you would specify the category ID on the animal row.

Sample tables:

Animals
-------
ID
Age
Sex
Species
Breed

Parameters
----------
ID
Name

Parameter Values
----------------
ParameterID
AnimalID
Value

Categories (optional - add CategoryID to animals)
---------------------
ID
Name

Category Parameters
-------------------
CategoryID
ParameterID

Answer 3

The name value pair solution can be done within the database.

In this example there are 4 tables, person, requirement, pet, petstat

Person and Pet each have an id and a name.
Each person also has one or more requirements. Requirements have a name, comparison, and a value.
Each pet has one or more PetStats which consist of a name and a value

with this setup, the short haired dogs could be found with a query like :

select * from pet, petstat
where pet.id = petstat.petId
 and petstat.name = 'hair'
 and petstat.value = 'short'
 and petId in (select petId from petstat
   where name='type' and value = 'dog')

The following query will match people to pets when all of the person's requirements are met by a pet.

select person.name, pet.name from person, pet 
where (select count(*) from requirement where requirement.personid = person.id)
 = (select  count(*) from requirement, petstat
    where requirement.comparison = 'eq' 
     and requirement.name = petstat.name
     and requirement.value = petstat.value
     and requirement.personId = person.id
     and petstat.petid = pet.id)
 + (select  count(*) from requirement, petstat
    where requirement.comparison = 'lt' 
     and requirement.name = petstat.name
     and requirement.value > petstat.value
     and requirement.personId = person.id
     and petstat.petid = pet.id)
 + (select  count(*) from requirement, petstat
    where requirement.comparison = 'gt' 
     and requirement.name = petstat.name
     and requirement.value < petstat.value
     and requirement.personId = person.id
     and petstat.petid = pet.id);

The less than and greater than comparisons could be handled better by adding numeric fields to the requirement and petstat tables and adding more pieces to the query, but this gives a good stat.

Here are the inserts to create the test data.

delete from person;
insert into person (id, name) values (1, 'Joe');
insert into person (id, name) values (2, 'Bill');
insert into person (id, name) values (3, 'Erik');
insert into person (id, name) values (4, 'Mike');

delete from pet;
insert into pet (id, name) values (1, 'spot');
insert into pet (id, name) values (2, 'mittens');
insert into pet (id, name) values (3, 'rover');

delete from requirement;
insert into requirement (personid, name, comparison, value) values (1, 'type', 'eq', 'dog');
insert into requirement (personid, name, comparison, value) values (1, 'color', 'eq', 'black');
insert into requirement (personid, name, comparison, value) values (2, 'type', 'eq', 'fish');
insert into requirement (personid, name, comparison, value) values (3, 'type', 'eq', 'dog');
insert into requirement (personid, name, comparison, value) values (3, 'hair', 'eq', 'long');

insert into requirement (personid, name, comparison, value) values (4, 'type', 'eq', 'dog');
insert into requirement (personid, name, comparison, value) values (4, 'weight', 'lt', '30');
insert into requirement (personid, name, comparison, value) values (4, 'weight', 'gt', '20');

delete from petstat;
insert into petstat (petId, name, value) values (1, 'type', 'dog');
insert into petstat (petId, name, value) values (1, 'color', 'black');
insert into petstat (petId, name, value) values (1, 'hair', 'short');
insert into petstat (petId, name, value) values (2, 'type', 'cat');

insert into petstat (petId, name, value) values (3, 'type', 'dog');
insert into petstat (petId, name, value) values (3, 'weight', '25');
insert into petstat (petId, name, value) values (3, 'color', 'brown');