javascript - SailsJS 模型和数据库中的外键

Question

我一直在努力更好地了解如何在 SailsJS 中设置外键。我目前正在开展一个类(class)项目，我的小组需要创建一个评估系统，其中包含教师和学生的个人资料以查看结果。我在网上看到了一些例子，但我看到了不同的格式，我不确定正确的格式应该是什么样子。

用户模型

/**
* User.js
*
* @description :: TODO: You might write a short summary of how this model works and what it represents here.
* @docs        :: http://sailsjs.org/#!documentation/models
*/

module.exports = {
  attributes: {
    // The user's anonymous ID (e.g. 1)
    anonymousID: {
        type: 'integer',
        autoIncrement: true
    },

    // The user's first name (e.g. Bob)
    firstName: {
      type: 'string',
      required: true
    },

    //The user's last name (e.g. Smith)
    lastName: {
      type: 'string',
      required: true,
    },

    // The user's full name (e.g. Bob Smith)
    fullName: {
      type: 'string',
      required: true
    },

    // The user's assigned NetID (e.g. abc123)
    netID: {
        type: 'string',
        primaryKey: true,
        required: true,
        unique: true
    },

    // The user's nine digit SchoolID (e.g. 000-000-000)
    schoolID: {
        type: 'integer',
        size: 9,
        required: true,
        unique: true
    },

    // The user's email address (e.g. netID@university.edu)
    email: {
        type: 'string',
        email: true,
        required: true,
        unique: true
    },

    // The encrypted password for the user (e.g. asdgh8a249321e9dhgaslcbqn2913051#T(@GHASDGA)
    encryptedPassword: {
      type: 'string',
      required: true
    },

    // The timestamp when the the user last logged in
    // (i.e. sent a username and password to the server)
    lastLoggedIn: {
      type: 'date',
      required: true,
      defaultsTo: new Date(0)
    },

    // The user's academic title (e.g. student)
    title: {
        state:{
            type : 'string',
            required: true,
            enum: ['Student', 'Faculty', 'Staff', 'Dean'],
        defaultsTo: 'Staff'
        }
    },

    // The user's academic classification (e.g. freshman)
    classification: {
        state: {
            type: 'string',
            required: true,
            enum: ['Freshman', 'Sophomore', 'Junior', 'Senior', 'Graduate', 'N/A']
        defaultsTo: 'N/A'
        }
    },

  }
};

调度模型

/**
* Schedule.js
*
* @description :: TODO: You might write a short summary of how this model works and what it represents here.
* @docs        :: http://sailsjs.org/#!documentation/models
*/

module.exports = {

  attributes: {
    // The CRN ID (e.g. 32458)
    courseID: {
        type: 'integer',
        autoIncrement: true,
        primaryKey: true
    },

    // The Course Reference Name (e.g. MBW 1001)
    course: {
        type: 'string',
        size: 8,
        required: true
        // Add FK code from Course Table
    },

    // The Course Name (e.g. Magical Basket Weaving)
    title: {
        type: 'string',
        required: true
        // Add FK code from Course Table
    },

    // The Course Instructor (e.g. ab123)
    intructorID: {
        type: 'string',
        required: true
        // Add FK code from User Table
    },

    // The Term refers to the semester (e.g. Fall 2015)
    term: {
        type: 'string',
        required: true
    },
  }
};

类(class)模型

/**
* Course.js
*
* @description :: TODO: You might write a short summary of how this model works and what it represents here.
* @docs        :: http://sailsjs.org/#!documentation/models
*/

module.exports = {

  attributes: {
    // The Evaluation ID (e.g. 1)
    courseNum: {
        type: 'integer',
        autoIncrement: true
    },

    // The Department Name (e.g. Arts and Sciences)
    department: {
        type: 'string',
        required: true
    },

    // The Course Reference Name (e.g. MBW 1001)
    course: {
        type: 'string',
        primaryKey: true,
        size: 8,
        unique: true
    },

    // The Course Name (e.g. Magical Basket Weaving)
    title: {
        type: 'string',
        required: true
    },

  }
};

注册模型

/**
* Enrolled.js
*
* @description :: TODO: You might write a short summary of how this model works and what it represents here.
* @docs        :: http://sailsjs.org/#!documentation/models
*/

module.exports = {

  attributes: {
    // The Transaction ID (e.g. 32458)
    transactionID: {
        type: 'integer',
        autoIncrement: true,
        primaryKey: true
    },

    // The CRN ID (e.g. 32458)
    courseID: {
        type: 'integer',
        required: true
      // Add FK code from Schedule Table
    },

    // The Course Instructor (e.g. ab123)
    instructorID: {
        type: 'string',
        required: true
        // Add FK code from Schedule Table
    },

    // The Course Instructor (e.g. ab123)
    studentID: {
        type: 'string',
        required: true
        // Add FK code from User Table
    },

    // The Term refers to the semester (e.g. Fall 2015)
    term: {
        type: 'string',
        required: true
    },

    // The Right to Submit an Evaluation (e.g. True or False)
    evaluationStatus: {
        type: 'boolean',
    },
  }
};

评价模型

/**
* Evaluation.js
*
* @description :: TODO: You might write a short summary of how this model works and what it represents here.
* @docs        :: http://sailsjs.org/#!documentation/models
*/

module.exports = {

  attributes: {
    // The Evaluation ID (e.g. 1)
    evaluationID: {
        type: 'integer',
        autoIncrement: true,
        primaryKey: true
    },

    // The user's anonymous ID (e.g. 1)
    anonymousID: {
        type: 'string',
        required: true,
        // Add FK code from user table
    },

    // The Course Instructor (e.g. ab123)
    intructorID: {
        type: 'string',
        required: true
        // Add FK code from User Table
    },

    // The course's assigned CRN (e.g. 12343)
    courseID: {
        type: 'integer',
        required: true,
        size: 5
        // Add FK code from schedule table
    },

    // The Course Reference Name (e.g. MBW 1001)
    course: {
        type: 'string',
        size: 8,
    },

    // The rating of question 1
    ratingOne: {
        type: 'integer',
        required: true,
        size: 2
    },

    // The rating of question 2
    ratingTwo: {
        type: 'integer',
        required: true,
        size: 2
    },

    // The rating of question 3
    ratingThree: {
        type: 'integer',
        required: true,
        size: 2
    },

    // The rating of question 4
    ratingFour: {
        type: 'integer',
        required: true,
        size: 2
    },

    // The rating of question 5
    ratingFive: {
        type: 'integer',
        required: true,
        size: 2
    },

    // The rating of question 6
    ratingSix: {
        type: 'integer',
        required: true,
        size: 2
    },

    // The rating of question 7
    ratingSeven: {
        type: 'integer',
        required: true,
        size: 2
    },

    // The rating of question 8
    ratingEight: {
        type: 'integer',
        required: true,
        size: 2
    },

    // The rating of question 9
    ratingNine: {
        type: 'integer',
        required: true,
        size: 2
    },

    // The rating of question 10
    ratingTen: {
        type: 'integer',
        required: true,
        size: 2
    },

    // The positive feedback from student
    positiveFeedback: {
        type: 'string',
        defaultsTo: 'N/A',
        size: 4000
    },

    // The negative feedback from student
    negativeFeedback: {
        type: 'string',
        defaultsTo: 'N/A',
        size: 4000
    },

    // The General Rating of Evaluation (e.g. 8.76, SUM(ratings)/TotalRatings)
    genRateEval: {
        type: 'float',
        required: true,
        size: 4
    },

    // The Inaproprate Flag (e.g. True or False)
    inaproprateFlag: {
        type: 'boolean',
    },
  }
};

我已经包括了我正在使用的所有五个模型，这样每个人都可以全面了解一切将/应该如何连接。

根据我的理解，外键应该像下面的代码片段一样设置。

调度模型(带外键)

/**
* Schedule.js
*
* @description :: TODO: You might write a short summary of how this model works and what it represents here.
* @docs        :: http://sailsjs.org/#!documentation/models
*/

module.exports = {

  attributes: {
    // The CRN ID (e.g. 32458)
    courseID: {
        type: 'integer',
        autoIncrement: true,
        primaryKey: true
    },

    // The Course Reference Name (e.g. MBW 1001)
    course: {
        // Add FK code from Course Table
      model: 'Course',
      via: 'course'
    },

    // The Course Name (e.g. Magical Basket Weaving)
    title: {
        // Add FK code from Course Table
      model: 'Course',
      via: 'title'
    },

    // The Course Instructor (e.g. ab123)
    intructorID: {
        // Add FK code from User Table
      model: 'User',
      via: 'netID'
    },

    // The Term refers to the semester (e.g. Fall 2015)
    term: {
        type: 'string',
        required: true
    },
  }
};

不过我不确定这是否是设置外键的正确方法。

Answer 1

是的，这是在 sails js 中设置外键的正确方法。也就是说，它因关联类型而异，即关系是一对一还是一对多。

以sailsjs网站为例，

一对一关系:

myApp/api/models/pet.js

module.exports = {

    attributes: {
        name:'STRING',
        color:'STRING',
        owner:{
            model:'user'
        }
    }

}

myApp/api/models/user.js

module.exports = {

    attributes: {
        name:'STRING',
        age:'INTEGER',
        pony:{
            model: 'pet'
        }
    }

}

一对多关系:myApp/api/models/pet.js

module.exports = {

    attributes: {
        name:'STRING',
        color:'STRING',
        owner:{
            model:'user'
        }
    }

}

myApp/api/models/user.js

module.exports = {

    attributes: {
        name:'STRING',
        age:'INTEGER',
        pets:{
            collection: 'pet',
            via: 'owner'
        }
    }

}

Sailsjs Associations