请教一个Mysql表设计问题。

问答

Zzhang962976642发布于 8 年前最后回复 8 年前

1143670

背景
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++对我这种小前端有帮助吗，个人想去学习一下，西佳佳。在此谢谢诸位了。

查看回复

zhang962976642#118 年前

引用 dengnan123建一个菜单表菜单之间要有层级关系，然后就生成关系树，直接返回前端，前端可以直接遍历显示

@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!");
});