数据库设计链接2个表到1，链接到几个

I currently have 3 main tables animal, food and medicine.

我目前有3个主要表动物,食品和药品。

From animal table I get tables:

从动物表我得到表:

1. species (cat,dog, bird, fish....)

物种(猫,狗,鸟,鱼....)

2. size(small,medium,large, big...)
3. age(puppy, young adult, old...)

年龄(小狗,年轻人,老...)

4. color (brown, black, gray... )

颜色(棕色,黑色,灰色......)

I want to store useful data of the medicine and food given to animals But I do not know how to link these data, is the following approach acceptable or what should I add or remove?

我想存储给予动物的药物和食物的有用数据但我不知道如何链接这些数据,以下方法是否可接受或我应该添加或删除什么?

My main question composite keys correctness and the way to retrieve data in queries...

我的主要问题是复合键的正确性以及在查询中检索数据的方式......

Animal

idAn SEX AGE COMMENT           SPECIES color  HAIR   SIZE
----------------------------------------------------------
1     M   1  without ear         1      1     LONG    1
2     F   2  blue eyed all gray  2      2     short   1

species

id name
-------
1  dog
2  cat
3  bird
4  fish
5  reptile
6  mouse  
7  other

age

id name
-------
1 puppy
2 young
3 adult
4 old

color

id  name
--------
1  black
2  gray
3  gold
4  green
5  red
6  brown

size

id  name
--------
1  small
2  medium
3  large
4  big

food

id name      label      
-------------------
1  sardine     so 
2  croquettes  dogchauw   
3  chicken     sirw
4  whiskas     whiskas  

food_Animal

idFood  idAnimal   quantity timesPerDay lastFood    LastWater
----------------------------------------------------------------------
2             1    70gr      3        12-12-12   12-12-12  
3             2    80gr      4        12-11-12   12-11-12

and for medicine something like the above.

对于像上面这样的药物。

What could be done or how to use it in MySQL

可以做什么或如何在MySQL中使用它

I was starting with something like

我是从类似的东西开始的

CREATE TABLE IF NOT EXISTS ANIMAL(
    idAn    int(3) NOT NULL AUTO_INCREMENT,
    sex     int(2) NOT NULL ,
    age     int(2) NOT NULL ,
    comment varchar(50)  ,                  
        species int(2) NOT NULL , 
    color   int(2) NOT NULL ,
        hair    varchar(50)     ,                   
        size    int(2) NOT NULL ,
    PRIMARY KEY (idAn)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

1 个解决方案

#1

/* Create a table with 2 fields, Entity and Name. Primary key is BOTH fields (as we know that Entity->Name will be a unique value) */
CREATE TABLE master_lookup (
  Entity nvarchar(10) not null,
  Name nvarchar(20) not null,
  primary key(Entity, Name)
);

/* Insert some data! */
INSERT INTO master_lookup VALUES 
('Species', 'Dog'),
('Species', 'Cat'), 
('Species', 'Bird'),
('Color', 'Black'),
('Color', 'Brown');

/* Let's have a look... */
SELECT * FROM master_lookup;
 Entity  | Name
----------------
 Species | Dog
 Species | Cat
 Species | Bird
 Color   | Black
 Color   | Brown

/* Et voila. One lookup table, ID numbers need not apply. Let's assume you have a form that will populate the Animal table with a list of species for the user to pick from. The source of the list is easy: */
SELECT Name FROM master_lookup WHERE Entity = 'Species';

/* Your INSERT query into Animal will look like this (let's say they're entering a small brown long-haired dog): */
INSERT INTO Animal ('M', 11, 'a comment', 'Dog', 'Brown', 'Long', 'Small');

/* And now when you SELECT from the animal table... */
SELECT * FROM Animal

idAn SEX AGE COMMENT           SPECIES color  HAIR   SIZE
----------------------------------------------------------
1     M   11  a comment         Dog    Brown  Long   Small

/* Look ma! No joins! Which means your query is much simpler and much faster! Yay! */