MySQL - 1 column table

Question

So I tried asking this yesterday however my phrasing of the question was pretty terrible. Im stuck designing a database, my system is basically hold different types of creatures, and there are three types of creatures. These are insects, air creatures and aquatic creatures, and so there are three seperate tables for these three. Each of these creatures have a base table and an active table so that a user can get a copy of this base creature. Now I want to be able to reference an id from these tables from another table and Im not sure how besides creating a new table with just an id and then each of these three base creatures having there id being a foreign key of this parent table.

Doing this though would mean that I would have a table which has only one column (ID) that is then referenced from 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 do you think? Is this ok to do?

So with a baseAquatic, baseInsect... tables, these store the base stats of that creature. However these havent been assigned to a user yet. In order to do that a new field in the activeCreature needs to be created so that it can generate an id to go in the activeAquatic, activeInsect... tables. The userID is specified in the activeCreatures table and not in the individual active tables. With an active creature it can have special stats that are only applicable to that one, so one activeinsect can have a different strength than another. The activeCreatures is the same as the baseCreatures as they have the same fields barring the userID. Also a basecreature can have a better percentage of appearing in one area than another. (Percentage table), this is the reason why I need to specify the basecreature ids in a seperate table so I can get that to use in the percentage table. Dyou think there is a more efficient way to do this? Another question I have is, on the percentage table, is there really any need for me to store the tableID AND the base id. Couldnt I just store the baseID and then get the id and the tableID from the baseCreatures table.

Another thought is that I could put generic columns such as the creature name in the baseCreatures table as well, but this may get confusing when laying out the information as the name wouldnt be with the other information in the creature specific tables.

Answer 1

SECOND EDIT (ORIGINAL CONTENT FOLLOWS AT '-----') Okay, I think I get it now, and see the need for the Base vs. Active.

Base Creature is like a species designation (Air table would have different species of bird). Active Creature is a specific instance of that species (like my cat Bob is an Active Creature of the (non-existent) Land-type Base Creature 'Cat').

In that case, I think your 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 for the Base tables are composite keys of the type id and the base id, so shark is 2-2, canary is 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 is 2-2-1).

The BaseCreatures table can then be built as a UNION of all air, aquatic, insect base tables, which can then be used in the Percentage table.

So I'm not really changing much of your ERD, just modifying the PKs to be more meaningful and modifying the 'Tables' table to be a 'Type' table (which might be more meaningful).

(Total aside: your ERD shows everything as being one-to-one relationships, when they're actually one-to-many, in case that matters.)

I hope that helps . . .

---

EDITED (this is the previous post content) - Your explanation of the need for the different tables for different animal types helps me a lot.

I can think of two approaches to handle your situation. One is to try to salvage my original answer :) the other is to go with your three table situation.

In the first case, you could try to fit all the animals into one table, with columns designed to work for all creature types:

If you have a set-up like 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 you can reference a given creature in your active table by referencing the creature_id in table 2. One reason to split the creature type in to a separate table is to reduce data redundancy. It also allows you (in future) to add additional info about each type (say, a description field for each creature type: 'insects are hexapod arthropods . . .') to the creature type table without having to update every record in the base or active creature tables.

This appraoch might work, but depends on the columns you want - and some may not work well across all creature types, which is why you have three separate creature-type tables.

In that case, I'd set up tables like this:

AirCreatures
id    name    type_id    column1       col2 and 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 and other type-specific info
------------------------------------------------------------------------
1     dolphin  2      (max time underwater?)  (is a fish?) etc
2     shark    2      etc
3     geoduck  2      etc

and then have

CreatureTypes
id   TypeName
1    Air
2    Aquatic
3    Insect

as you suggest.

Then, when you go to base or active table, you can reference specific creatures with a composite primary key consisting of id and type_id (1-2 is sparrow, 2-3 is geoduck, etc).

Answer 2

I suggest that your list of attributes (Air, Aquatic) will grow too long to be practical to implement as tables.

Suggest you start without the normalization (ids); it will be much easier to sketch out. Later you can normalize if it is warranted.

Animal  : attribute
------  : ---------
Beetle  : insect
Beetle  : flying
Sparrow : flying
Bat     : flying
Bat     : mammal
Whale   : mammal
Whale   : aquatic

SELECT animal FROM tbl WHERE attribute = 'flying';

would give you 3 rows: Beetle, Sparrow, Bat.

Later, you may want a table for animals with columns like: id, common_name, latin_name, general_size, endangered, ... At that point, you would change to

Animal_id : attribute
------  : ---------
1       : insect
1       : flying
2       : flying
...
and
Animal_id : common_name : latin_name ...
1           Beetle        ...
2           Sparrow       ...
3...

You won't necessarily every need to 'normalize' the attributes.