database - MySQL - 1 column table -

so tried asking yesterday phrasing of question pretty terrible. im stuck designing database, system hold different types of creatures, , there 3 types of creatures. these insects, air creatures , aquatic creatures, , there 3 seperate tables these three. each of these creatures have base table , active table user can copy of base creature. want able reference id these tables table , im not sure how besides creating new table id , each of these 3 base creatures having there id being foreign key of parent table.

doing though mean have table has 1 column (id) referenced other tables.

basecreatures table:

id

---

1
2
3
4

aircreatures table:

id name

---

1 eagle
2 bird

aquaticcreatures table:

id name

---

3 whale
4 shark

what think? ok do?

so baseaquatic, baseinsect... tables, these store base stats of creature. these havent been assigned user yet. in order new field in activecreature needs created can generate id go in activeaquatic, activeinsect... tables. userid specified in activecreatures table , not in individual active tables. active creature can have special stats applicable one, 1 activeinsect can have different strength another. activecreatures same basecreatures have same fields barring userid. basecreature can have better percentage of appearing in 1 area another. (percentage table), reason why need specify basecreature ids in seperate table can use in percentage table. dyou think there more efficient way this? question have is, on percentage table, there need me store tableid , base id. couldnt store baseid , id , tableid basecreatures table.

another thought put generic columns such creature name in basecreatures table well, may confusing when laying out information name wouldnt other information in creature specific tables.

second edit (original content follows @ '-----') okay, think now, , see need base vs. active.

base creature species designation (air table have different species of bird). active creature specific instance of species (like cat bob active creature of (non-existent) land-type base creature 'cat').

in case, think tables should be:

type  id        name -------------------   1        air   2        aquatic    etc  baseair  type_id    id      name     type_specific_attribute ----------------------------------------------------     1       1       eagle    (wing-span?)     1       2       canary   (wing-span?)     etc  baseaquatic  type_id    id      name     type_specific_attribute ----------------------------------------------------     2       1       dolphin  (number of fins?)     2       2       shark    (number of fins?)  etc 

primary keys base tables composite keys of type id , base id, shark 2-2, canary 1-2.

for active creatures, same idea:

activeair type_id  base_id  id   name     specific_characteristic --------------------------------------------------------    2        1      1   flipper  (swim speed?)    2        1      2   opo      (swim speed?)    2        2      1   jaws     (swim speed?)  etc 

again, composite pk of type_id-base_id-id (jaws 2-2-1).

the basecreatures table can built union of air, aquatic, insect base tables, can used in percentage table.

so i'm not changing of erd, modifying pks more meaningful , modifying 'tables' table 'type' table (which might more meaningful).

(total aside: erd shows being one-to-one relationships, when they're one-to-many, in case matters.)

i hope helps . . .

---

edited (this previous post content) - explanation of need different tables different animal types helps me lot.

i can think of 2 approaches handle situation. 1 try salvage original answer :) other go 3 table situation.

in first case, try fit animals 1 table, columns designed work creature types:

if have set-up this:

table 1: creature type table type_id   type_name ------------------- 1         insect 2         air 3         aquatic  table 2: base creature table creature_id    name        type (from table 1)   #appendages       size ------------------------------------------------------------------------ 1              beetle      1                     2 wings, 6 legs   small 2              dolphin     3                     3 fins            medium 3              sparrow     2                     2 wings, 2 legs   small 4              eagle       2                     etc 5              trout       3 

then can reference given creature in active table referencing creature_id in table 2. 1 reason split creature type in separate table reduce data redundancy. allows (in future) add additional info each type (say, description field each creature type: 'insects hexapod arthropods . . .') creature type table without having update every record in base or active creature tables.

this appraoch might work, depends on columns want - , may not work across creature types, why have 3 separate creature-type tables.

in case, i'd set tables this:

aircreatures id    name    type_id    column1       col2 , other type-specific info ------------------------------------------------------------------------ 1     eagle    1      (wing span?)  (feather length) etc 2     sparrow  1      etc 3     bat      1      etc  aquaticcreatures id    name    type_id    column1       col2 , other type-specific info ------------------------------------------------------------------------ 1     dolphin  2      (max time underwater?)  (is fish?) etc 2     shark    2      etc 3     geoduck  2      etc 

and have

creaturetypes id   typename 1    air 2    aquatic 3    insect 

as suggest.

then, when go base or active table, can reference specific creatures composite primary key consisting of id , type_id (1-2 sparrow, 2-3 geoduck, etc).