Databases
StackOverflow2013
Postsdbo
POHow to redesign this SQL data structure?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to redesign this SQL data structure?
    primarykey
    data
    text
    <p>I have a data structure used for storing information about animals. Types of data (columns) depends on animal type, only a few are common. There can be many animal types with different feature sets (one type can implement one or more feature sets). </p> <p><strong>What I want to achieve is:</strong></p> <ol> <li><p>to have a possibility to create new animal types, based on existing feature sets without modifying data structure later</p></li> <li><p>to have a possibility to make simple selects of all data about animal with known animal_id or all animals with known type</p></li> <li><p>to have a data control (to make sure that inserting data e.g. about flying_animal wouldn't be allowed for animal type 'Dog')</p></li> <li><p>to Keep It as Simple and Stupid as possible (of course now I can write some stored procedures and triggers "before insert" to make sure that all data is correct, but maybe there is more elegant way?)</p></li> </ol> <p><strong>Existing data structure:</strong></p> <p><strong>Table:</strong> animals</p> <p>animal_id (Primary Key)</p> <p>animal_name</p> <p>animal_age</p> <p>animal_type (Foreign Key from animal_types table)</p> <p><strong>Example data row:</strong></p> <p>1, 'Rex', 3, 2</p> <p><strong>Table:</strong> animal_types</p> <p>animal_type_id : int (Primary Key)</p> <p>animal_type_name : char</p> <p><em>-- Here are feature sets:</em></p> <p>house_animal : boolean</p> <p>flying_animal : boolean</p> <p>wild_animal : boolean</p> <p><strong>Example data rows:</strong></p> <p>1, 'Wild parrots', false, true, true</p> <p>2, 'Dogs', true, false, false</p> <p>Every feature set is now implemented as table, e.g.:</p> <p><strong>Table:</strong> house_animal</p> <p>animal_id (Foreign Key from animals table)</p> <p>favorite_food</p> <p>last_vet_visit_date</p> <p><strong>Table:</strong> flying animal</p> <p>animal_id (Foreign Key from animals table)</p> <p>length_of_wings</p> <p>max_speed</p> <p>etc.</p> <p>Current data structure makes things too complicated.</p> <p>You can ask me why not to make one big table with all possible columns and one dictionary table with animal types. Of course I can, but that makes data control almost impossible, and how to define a new type with only some of the columns allowed..?</p> <p>And why wouldn't I create one table for every new type? Because I don't want to have e.g. 200 tables with only a few rows in each of them.</p> <p>Any ideas?</p> <p>DBMS is PostgreSQL version 9.1.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScathulhu
    1. UScathulhu
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to redesign this SQL data structure?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to redesign this SQL data structure?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to redesign this SQL data structure?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COpossible duplicate of [How to implement a super class, sub class relationship in the database?](http://stackoverflow.com/questions/3180925/how-to-implement-a-super-class-sub-class-relationship-in-the-database)
      singulars
      1. POHow to redesign this SQL data structure?
      1. USSjoerd
    2. COCan you tell us more about how this data set will be used, and what kind of application will be maintaining it and accessing it?
      singulars
      1. POHow to redesign this SQL data structure?
      1. USDavid Aldridge
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload