blocks|key|3033992|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|entityMap^0^^$0|@$1|2|3|-4|4|5|6|B|7|@]|8|@]|9|$]]]|A|$]]

You have several options:

<h2>Create one table with lots of columns</h2>

This is what <a href="https://dba.stackexchange.com/users/143823/gerard-h-pille">Gerard H. Pille</a> suggested in the comments: create a single table, with all possible columns for all possible processes. Where a process does not have a value for a given column, set the value to NULL.

Pros:

<ul>
<li>Relatively easy to query and understand</li>
</ul>

Cons:

<ul>
<li>Meaning of NULL in a column becomes questionable: Is there no defined value for the column for this process, or does this column not apply to this process.</li>
<li>Table may require update with new processes, or changes to existing processes.</li>
<li>Many DBMSes have a maximum number of columns per table; if you have enough processes and unique columns per process, you could hit that limit.</li>
<li>More difficult to enforce referential integrity at the DB level, especially if certain columns should not be NULL for certain processes, but aren't used in every process.</li>
</ul>

<h2>One table per process</h2>

Here, you would have a different table for each process. These tables would presumably have some columns in common in every table; some that only appear in some of the tables; and some unique to a given table.

Pros:

<ul>
<li>Meaning of NULL in any column is clear.</li>
<li>Enforcing referential integrity is relatively easy.</li>
<li>Less likely to have problems with hitting maximum number of columns.</li>
<li>Easy to query within a given process.</li>
</ul>

Cons:

<ul>
<li>Have to create a new table for every new process.</li>
<li>Changes that impact all processes may require the same basic updates to all process tables.</li>
<li>Somewhat painful to query data from all processes.</li>
<li>Harder to maintain a unique global process ID, without a central table to work off.</li>
</ul>

<h2>Base process table, with EAV table</h2>

Create a base Process table to store the data common to all processes (proc date, time in/out, qty start). Then, create a table to hold all other attributes for each process. This table would have three basic columns:

<ul>
<li>the ID of the base process record (a foreign key pointing to the base Process table)</li>
<li>The name or ID of the attribute being stored (e.g. mc#, yld, epotek batch #)</li>
<li>the actual value for the given attribute</li>
</ul>

Pros:

<ul>
<li>Only store attributes that belong to the base Process table's process type</li>
<li>Changes to base process confined to one table; changes to other processes require no DB structural changes</li>
<li>No issue with hitting maximum number of columns.</li>
</ul>

Cons:

<ul>
<li>requires more disk space to store (in most cases)</li>
<li>More difficult to query, especially if <code>WHERE</code> clause needs to check values of several different attributes stored in EAV table</li>
<li>More difficult to enforce data integrity at the DB level (to ensure that EAV attributes are set for every base process row of a given process type, you basically need a metadata table that defines which attributes are required and which are optional, and you have to check that after each insert/update of a relevant base Process row is complete, or some similar process). If you do want to allow multiple entries for certain attributes, it becomes even more difficult to ensure your distributed Process row is valid.</li>
</ul>

<h2>Hybrid approach</h2>

Create a base process table with all column common to all processes. Create full or partial process-specific tables, with columns unique to a given process. you could even allow an EAV table for attributes that are always optional, or possibly that are new to the system.

Pros:

<ul>
<li>This can help prevent problems with the number of columns in a table.</li>
<li>Changes to the base process data happen in one place.</li>
<li>Maintaining data integrity should be easier.</li>
<li>Most querying is relatively simple.</li>
</ul>

Cons:

<ul>
<li>Have to create a new table for every new process.</li>
<li>Changes that move columns that were previously present in some process-specific tables to the base process table require a fair amount of maintenance work.</li>
</ul>

<h2>Other considerations</h2>

If you have a large number of fields that are rarely needed for sorting/searching, but just for display in reporting, then storing certain values as JSON (basically, a data structure materialized as a string value) may work well. Some DBMSes have been extended to explicitly handle JSON data, and even to allow it to be indexed.

Also, some databases may allow some sort of table inheritance. I don't know much about this, but if you have a lot of processes that are the same except for one or two unique data fields, this might allow you to set up a series of table types, all built off your base table; this could make maintenance of a "one table per process" solution easier, if a change to the base process table type would essentially flow through all the process-specific tables.

<hr />

All well and good. However, in practice, the details of your specific application and of your specific data will really drive this.

If most columns are present in most processes, then a single table with a lot of columns may make a lot of sense.

If your processes are frequently in flux, a base table plus an EAV table may be the only sane solution; otherwise, you may find yourself constantly needing to alter your databases structures.

If the number of rows you'll have for certain processes will be much more significant than the rows for other processes, then it may make sense for the more significant processes to have all columns in the base process table, and to use extra tables or an EAV for the processes that will appear less frequently.

Note that different DBMSes will have their own strengths and weaknesses; some may perform better than others for a given set-up.

You also have to take your application into account. If you're already going to have to make a specific form/page for each process (reusing elements where possible, of course), then the idea of one table per process may make very good sense; If, on the other hand, you'll have a central form with the data common to all processes, and will simply list each additional attribute and value at the bottom of the common form, an EAV table would match your structure logically.

Ultimately, what's best for you will come down to the specifics of the problems your application is intended to resolve; the amount of time you expect to spend in maintenance on it; the size of maintenance windows available; the performance needs of your specific situation; and your (or your company's) preferred coding style and approach.

Obviously, we haven't seen all of your processes. If, however, almost all possible columns are used by multiple processes, and if there are no more than two or three non-common columns per process, and if your set of processes is well-defined (so new columns are unlikely to be needed), I would be inclined to use a single <code>Process</code> table, and have all the columns in it. Again, note all the caveats in the preceding sentence, and realize that's a personal preference - others might pick a different direction.

I'm planning to develop a Shopfloor Database System. What are the best database design pattern should i apply to my system. Each of the process have their own form and own field, but most of the field set are similar with each other process, just some of the process may have additional extra field. In which, it was difficult for me to create just one table to keep all of the process data entry.

Below are the sample of my process and the field column that required for each of the process.<a href="https://i.stack.imgur.com/Fx0sF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Fx0sF.png" alt="enter image description here"></a>

Which are the recommend database design should i apply to this? Should I just create one table to keep all those similar column field data and for the rest of the other different column name I just move it to other table with a foreign key?

What is the Best Database Design for multiple form which have some similar column, but some of it does have extra column that other form does not have

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我正计划开发一个车间数据库系统。什么是最好的数据库设计模式，我应该应用到我的系统。每个进程都有自己的表单和字段，但大多数字段集与其他进程相似，只是一些进程可能有额外的字段。在这种情况下，我很难只创建一个表来保存所有的流程数据输入。下面是我的流程示例和每个流程所需的字段列。📷我应该应用哪一种推荐的数据库设计？我是否应该创建一个表来保存所有类似的列字段数据，而对于其他不同的列名，我应该只使用外键将其

问有一些相似列的多个表单的最佳数据库设计是什么，但其中有些表单有其他表单没有的额外列
EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有一些相似列的多个表单的最佳数据库设计是什么，但其中有些表单有其他表单没有的额外列EN