帮助数据库建模

发布于 2024-11-02 18:20:54 字数 635 浏览 0 评论 0原文

我有两张桌子：一张用于领域（如科学、体育、教育），另一张用于专业（如科学家、设计师、高尔夫球手）。两个表之间存在外部关系，目前运行没有任何问题。

但现在我需要另一个表来放置“工人数量”、“平均年龄”、“在公司工作的年限”（这个列表可能每个职业都不同）。最好的方法是什么？创建另一个表？父母会是什么？基本上，这是第三种说法。

CREATE TABLE group (  
    id smallint(5) unsigned NOT NULL auto_increment,  
    area varchar(30),  
    PRIMARY KEY (id) 
)

CREATE TABLE job (  
    ref int(10) unsigned NOT NULL auto_increment,  
    jobid smallint(5) unsigned NOT NULL,  
    job varchar(50),  
    PRIMARY KEY (ref)  
)

    ALTER TABLE job
    ADD CONSTRAINT FK_job
    FOREIGN KEY (jobid) REFERENCES group(id)  
    ON UPDATE CASCADE  
    ON DELETE CASCADE;

原文

I have two tables: one for areas (like science, sport, education), and another for professions (like scientist, designer, golf player). There is a foreign relationship between the two tables, which works without any problems at the moment.

But now I need another table to put "number of workers", "average age", "years in the company" (this list is possibly different for each profession). What is the best way to do this? Create another table? What would be the parent? Basically, it is a third statement.

CREATE TABLE group (  
    id smallint(5) unsigned NOT NULL auto_increment,  
    area varchar(30),  
    PRIMARY KEY (id) 
)

CREATE TABLE job (  
    ref int(10) unsigned NOT NULL auto_increment,  
    jobid smallint(5) unsigned NOT NULL,  
    job varchar(50),  
    PRIMARY KEY (ref)  
)

    ALTER TABLE job
    ADD CONSTRAINT FK_job
    FOREIGN KEY (jobid) REFERENCES group(id)  
    ON UPDATE CASCADE  
    ON DELETE CASCADE;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半仙 2024-11-09 18:20:54

据我了解，我将设置第三个表，如下所示。

Table: Employee
First_Name varchar(30)
Last Name varchar(30)
Age (int(3))
Employment Date (DATE)
Active (Yes/No)
JobFK (Points to emprego.PK)

通过这种设置，您可以在表上使用联接来计算有多少工人从事同一职业。这些员工的平均年龄以及他们在公司工作的时间。给出有关当前表的更多信息，我什至可以描述该信息的 sql 查询。

From what I understand I would set up a third table as follows

Table: Employee
First_Name varchar(30)
Last Name varchar(30)
Age (int(3))
Employment Date (DATE)
Active (Yes/No)
JobFK (Points to emprego.PK)

With this kind of setup you can use joins on your tables to calculate how many workers are in the same profession. The average age of those employees, and how long they have been with the company. Given more information about your current tables I could even describe the sql queries for that information.

回复收藏 0 原文

~没有更多了~