Re: Database Design Best Practice help

From: Jose Soares <jose.soares_at_sferacarta.com>
Date: Mon, 28 Jan 2013 18:08:27 +0100
Message-ID: <5106B08B.2060600_at_sferacarta.com>

These tables are simple coding tables used for selection list , here some:

specie
  code | description
--------+---------------

  0801   | Poultry
  0128   | Rabbit
  0132   | Turkey
  0130   | Bees
  1      | Sheep & Goats
  3      | Cattle
  6      | Camels
  0129   | Buffalo
  20     | Fish
  0140   | Ostrich
  61     | Hare
  72     | Trouts
  74     | Carp
  77     | Cat Fish
  84     | Oyster
  85     | Clam
  0141   | Duck
  0137   | Goose
  0134   | Quail
  0135   | Partridge

movement
  code |        description
------+-------------------------------
    11 | Theft
    12 | Move out to slaughter house
    15 | Home slaughtering
    17 | Export to third countries
    16 | Export to EU countries
     1 | Birth
     2 | First tagging
     5 | Move in
     4 | Import from third countries
     8 | Move out
    14 | Death
    18 | Reidentification for lost ID
     7 | Move in from Temporary Export
    10 | Move out to  Temporary Export
     3 | Import

desease
  code | description

--------+----------------------------------------------------
  ATH    | Antrax (Multiple Species)
  BTV    | Blutongue (Multiple Species)
  002    | Rabies
  001    | TBC
  OVS    | Old world screwworm/Chrysomya bezziana(Multiple Sp
  BRM    | Brucellosis/Bucella Melitensis (Multiple Species)
  FMD    | Foot and mouth disease (Multiple Species)
  BRU    | Brucellosis/Brucella Abortus (Multiple Species)
  NWS    | New world screwworm/Cochliomyia (Multiple Species)
  PTB    | Paratubercolosis (Multiple Species)
  RAB    | Rabies (Multiple Species)
  RVF    | Rift Valley Fever (Multiple Species)
  RP     | Rinderpest (Multiple Species)
  VS     | Vesicular Stomatitis (Multiple Species)
  EAE    | Enzootic abortion of ewes (Sheep and Goats)
  PPR    | Peste des petits ruminants (Multiple Species)
  SCP    | Scrapie (Sheep and Goats)
  SGP    | Sheep pox and goat pox (Sheep and Goats)
  BAM    | Bovine anaplasmosis (Cattle)
  IB     | Avian infectious bronchitis (Poultry)
  EEL    | Equine Encephalimyelitis
  EVA    | Equine Viral Arteritis
  ND     | Newcastle Disease
  HPA    | Higth Pathogenic Avian Influenza
  CCP    | Contagious Caprine Pleuropneumonia
  CBP    | Contagious Bovine Pleuropneumonia
  PC     | Paratubercolosis Cattle

On 01/28/2013 02:57 PM, John Hurley wrote:
> I guess my first question is do you know "why you have about one hundred tables
> like this ..." in the current database?
>
> Do they represent different entities in some fashion that correspond to some
> part of the real world?
>
> Do these tables ONLY have the two columns code and description or is there all
> sorts of other columns in ( some/all ) of them?
>
> Do you have any understanding of the history of how and why the current set of
> tables were ( pick one ) created/designed/arrived in your database?
>
>
>
> ----- Original Message ----
> From: Jose Soares <jose.soares_at_sferacarta.com>
> To: oracle-l_at_freelists.org
> Sent: Mon, January 28, 2013 4:03:16 AM
> Subject: Database Design Best Practice help
>
> Hi all,
>
> I have a question about database design best pratice.
>
> In my db I have about one hundred tables like this:
>
> code
> description
>
> To avoid to have a so great number of similar tables in the db
> I wonder if it is a good idea to unify all these tables in one big table
> like this:
>
> id
> code
> table_ name
> description
>
> The advantages are:
>
> 1. only one table in the db instead of 100
> 2. only one controller to manage the table
>
> Could this be a way to enhance db performance?
> Is there any negative point that I don't see?
>
> Thanks for any comments.
>
> j
>
>
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 28 2013 - 18:08:27 CET