请教一个Mysql表设计问题。
背景
leader跟我说，想在后台新增一个功能模块，就是后台可视化配置前端项目的菜单信息。由于我不怎么了解后台，其实是，公司人员紧张，只能让我来去做。。。
问题
实现以上需求，我认为需要存在数据库中，此项目为上线项目，技术选型为react，后端数据库为node+mysql(sequelize)。我认为后台需要配备一张router表，但发现，前端的路由菜单是父子嵌套的，我不知道这个router表如何设计，所以来cnodejs请教一下。
前端需要的菜单结构
const routerConfig = [
{ path: "/", component: "/pages/home", name: "首页", key: "index", icon: "i-home", hideNav:false },
{ path: "/cardbox", component: "/pages/cardbox", name: "仪表盘", key: "cardbox", icon: "i-cardbox", hideNav: false,openItem: true
routes: [
{ path: "/cardbox/info", component: "/pages/cardbox/info", name: "分析页", key: "info", icon: "i-xxx", hideNav: false },
{ path: "/cardbox/workplace", component: "/pages/cardbox/workplace", name: "工作台", key: "workplace", icon: "i-xxx2", hideNav: false }
]
},
{ path: "/search", component: "/pages/search", name: "搜索页", key: "search", icon: "i-search", hideNav: false,openItem: false
routes: [
{ path: "/search/article", component: "/pages/search/article", name: "搜索文章", key: "article", icon: "i-xxx", hideNav: false },
{ path: "/cardbox/project", component: "/pages/search/project", name: "搜索项目", key: "project", icon: "i-xxx2", hideNav: false },
{ path: "/cardbox/application", component: "/pages/search/application", name: "搜索应用", key: "application", icon: "i-xxx3", hideNav: false }
]
}
];
目前经过我翻阅百度，尝试一下设计:
个人认为这种方式并不理想，在使用时，需要前台，自己循环迭代数组，通过parentid去组装所需的数据结构。
其次，考虑过存储JSON字符串，提议过pt库，考虑过mongodb，但是提议全被老大直接pass，我让你实现个功能模块，不是让你给我重构项目，工资还想要吗一句反问打的不敢说话，还有弱弱的问一句，公司想培养一名c++工程师，对于将来的职业道路来讲，学习c++对我这种小前端有帮助吗，个人想去学习一下，西佳佳。
在此谢谢诸位了。
11 回复
树结构都需要自己手动遍历的，属于无限极分类吧。
https://nodelover.me/course/db-design-1 具体忘记了，自己看吧。
https://github.com/MiYogurt/db-design/
存 json 串没啥问题。反正这个 json 又不大。
@AnzerWall 所以我上面那种设计方式可以是吧，只不过要在nodejs中select出来所有数据，我自己去for一边，拼好格式，再给他发给前端json把。
@MiYogurt 考虑到json字符串了，但是老大说，你每次取出来都得parse一边，然后我每次修改某个值，你还得重新update一次，这样不好。。。我也不知道他指的哪里不好，是浪费性能还是什么东西，我也不敢问，怕被骂。。
@dengnan123 您好我这样尝试了一下。但是还是不知道怎么设计好，请您在指点一下。
两个表我通过第一个表left join关联第二表menu_config的target Menu去查。然后menu_config再去left join查mene表的数据- -。
这是我得到的结果，但是我发现这种方式，父子没问题，但是子菜单的子菜单，查不到，望大佬在指点一下，谢谢。
// 这是查询到的json
[
{
"id": 1,
"path": "index",
"name": "首页",
"component": "pages/app",
"key": "index",
"icon": "i-home",
"hideItem": 0,
"checkItem": 1,
"index": 0,
"created_at": "2018-09-21T14:37:25.000Z",
"updated_at": "2018-09-21T14:37:27.000Z",
"deleted_at": null,
"menu_configs": []
},
{
"id": 2,
"path": "cardbox",
"name": "仪表盘",
"component": "pages/cardbox",
"key": "cardbox",
"icon": "i-cardbox",
"hideItem": 0,
"checkItem": 0,
"index": 0,
"created_at": "2018-09-21T14:38:05.000Z",
"updated_at": "2018-09-21T14:38:08.000Z",
"deleted_at": null,
"menu_configs": [
{
"targetMenu": 2,
"menu": {
"id": 4,
"path": "cardbox/workerspace",
"name": "工作台",
"component": "pages/cardbox/workerspace",
"key": "cardbox/workerspace",
"icon": null,
"hideItem": 0,
"checkItem": 0,
"index": 1,
"created_at": "2018-09-21T14:39:31.000Z",
"updated_at": "2018-09-21T14:39:33.000Z",
"deleted_at": null
}
},
{
"targetMenu": 2,
"menu": {
"id": 3,
"path": "cardbpx/list",
"name": "仪表列表",
"component": "pages/cardbox/list",
"key": "cardbox/list",
"icon": null,
"hideItem": 0,
"checkItem": 0,
"index": 1,
"created_at": "2018-09-21T14:38:41.000Z",
"updated_at": "2018-09-21T14:38:43.000Z",
"deleted_at": null
}
}
]
},
{
"id": 5,
"path": "search",
"name": "查询",
"component": "pages/search",
"key": "search",
"icon": "i-search",
"hideItem": 0,
"checkItem": 0,
"index": 0,
"created_at": "2018-09-21T14:40:26.000Z",
"updated_at": "2018-09-21T14:40:28.000Z",
"deleted_at": null,
"menu_configs": [
{
"targetMenu": 5,
"menu": {
"id": 6,
"path": "search/article",
"name": "查询文章",
"component": "pages/search/article",
"key": "search/article",
"icon": null,
"hideItem": 0,
"checkItem": 0,
"index": 1,
"created_at": "2018-09-21T14:41:20.000Z",
"updated_at": "2018-09-21T14:41:22.000Z",
"deleted_at": null
}
},
{
"targetMenu": 5,
"menu": {
"id": 7,
"path": "search/application",
"name": "查询App",
"component": "pages/search/application",
"key": "search/application",
"icon": null,
"hideItem": 0,
"checkItem": 0,
"index": 1,
"created_at": "2018-09-21T14:42:06.000Z",
"updated_at": "2018-09-21T14:42:09.000Z",
"deleted_at": null
}
}
// ps 这里少了application/：id的数据信息没查到，就是上面这个application的子菜单没查到。
]
}
]；
// 这里是代码，由于是测试写的比较渣。
const express = require("express");
const _sequelize = require("./db.js");
const menu_config = _sequelize.sequelize.import("./menu_config.js");
const menu = _sequelize.sequelize.import("./menu.js");
menu.hasMany(menu_config, {
foreignKey: "targetMenu",
constraints: false
});
menu_config.belongsTo(menu, {
foreignKey: "menuId",
targetKey: "id",
constraints: false
});
let app = new express();
app.get("/models",(req,res,next) => {
_sequelize.sequelize.sync();
res.send("ok");
});
app.get("/", (req,res,next) => {
let model;
model = _sequelize.sequelize.models;
model.menu.findAll({
include: [{
model: menu_config,
attributes: {
exclude: ["menuId", "id", "created_at", "updated_at", "deleted_at"]
},
include: [{
all: true
}],
order: [[ "id", "asc" ]]
}],
where: {
index: 0
},
order:[["id", "asc"]]
})
.then(data => {
res.json(data);
})
.catch(err => {
console.log(err)
})
});
app.use((err, req, res, next) => {
console.log(err);
});
app.listen(5000, () => {
console.info("start!");
});
