use sequence object for multiple tables to ensure querability on join

I am using Oracle. I have two tables:

Cat (cat_ID, cat_name, cat_age, cat_strength)

Dog (dog_ID, dog_name, dog_age)

Sometimes I need to get all pets into one query result. I was thinking of creating an animal_seq sequence that can be used by both Cat开发者_C百科 and Dog tables so that they never have duplicate IDs across tables and then when joined can be easily searched/queried whatever.

Is this bad practice? If so, why? Are there better ways to design the tables (eg just one Animal table, or multiple inheritence). Personally, I try to avoid inheritence due to the performance issues of joins.

Having a single sequence is totally acceptable and safe. Oracle ensures multiple reads to a sequence always returns a unique value so you shouldn't have any problems with duplicate keys.

If the the schema between CAT and DOG is truly unique and an additional animal entity will also be unique, I would keep separate tables. If you are going to maintain the same information about cats, dogs, monkeys, etc., I would recommend putting them into a single ANIMAL table. You'll have to give more information about the application/database for us to know what to recommend.

With your current design what if you wanted to record birds or rabits or any other animal ?You would have to create tables for each type.

I would say use KISS(Keep it simply stupid) principle and have one table and join to another table called ANIMAL_TYPE( animal_type_id,Animal_type_name) that way you can make the sure ids are not duplicate and you can tend should you want to record other animal types.

I think you are looking for a base table, animal. Then you have two sub classes cat and dog. Such a design will help you when you add information such as "owner" of that animal, or "animal observation" or whatever the purpose of your application is.

table animal(
   animal_id 
  ,animal_type <-- Discriminator column with for example C for cat, D for dog 
  ,name
  ,age
  ,primary key(animal_id)
)

table cat(
   animal_id
  ,cat_strength
  ,primary key(animal_id)
  ,foreign key(animal_id) references animal(animal_id)
)

table dog(
   animal_id
  ,dog specific attributes here
  ,primary key(animal_id)
  ,foreign key(animal_id) references animal(animal_id)
)

As you can see, I've moved up common attributes to the base table animal, while keeping the specific attributes for the subclasses in the sub class tables.