Barry Williams has published a library of about six hundred data models for all sorts of applications. Almost certainly it will give you a "starter for ten" for all your subsystems. Access to this library is free so check it out.
It sounds like this is a big "enterprise-y" application your organisation wants, and you seem to be a bit of a beginner with databases. If at all possible you should start with a single sub-system - say, Orders - and get that working. Not just the database tables build but some skeleton front-end for it. Once that is good enough add another, related sub-system such as Billing. You don't want to end up with a sprawling monster.
Also make sure you have a decent data modelling tool. SQL Power Architect is nice enough for a free tool.
Before you start read up on normalization until you have no questions about it at all. If you only did this in school, you probably don't know enough about it to design yet.
Gather your requirements for each module carefully. You need to know:
Business rules (which are specific to applications and which must be enforced in the database because they must be enforced on all records no matter the source),
Are there legal or regulatory concerns (HIPAA for instance or Sarbanes-Oxley requirements) security (does data need to be encrypted?)
What data do you need to store and why (is this data available anywhere else)
Which pieces of data will only have one row of data and which will need to have multiple rows?
How do you intend to enforce uniqueness of the the row in each table? Do you have a natural key or do you need a surrogate key (suggest a surrogate key in almost all cases)?
Do you need replication?
Do you need auditing?
How is the data going to be entered into the database? Will it come from the application one record at a time (or even from multiple applications)or will some of it come from bulk inserts from an ETL tool or from another database.
Do you need to know who entered the record and when (highly likely this will be necessary in an enterprise system.
What kind of lookup tables will you need? Data entry is much more accurate when you can use lookup tables and restrict the users to the values.
What kind of data validation do you need?
Roughly how many records will the system have? You need to have an idea to know how big to create your test data.
How are you going to query the data? Will you be using stored procs or an ORM or dynamic queries?
Some very basic things to remember in your design. Choose the right data type for your data. Do not store dates or numbers you intend to do math on in string fields. Do store numbers that are not candidates for math (part numbers, zip codes, phone numbers, etc) as string data as you may need leading zeros. Do not store more than one piece of information in a field. So no comma-concatenated lists (these indicate the need for a related table) and while you are at it if you find yourself doing something like phone1, phone2, phone 3, stop right away and design a related table. Do use foreign keys for data integrity purposes.
All the way through your design consider data integrity. Data that has no integrity is meaningless and useless. Do design for performance, this is critical in database design and is NOT premature optimization. Database do not refactor easily, so it is important to get the most critical parts of the performance equation right the first time. In fact all databases need to be designed for data integrity, performance and security.
Do not be afraid to have multiple joins, properly indexed these will perform just fine. Do not try to put everything into an entity value type table. Use these as sparingly as possible. Try to learn to think in terms of handling sets of data, it will help your design. Databases are optimized to do things in sets.
There's more but this is enough to start digesting.
Try to keep your concerns separate here. Users being able to update the database is more of an "application design" problem. If you get your database design right then it should be a case of developing a nice front end for it.
First thing to look at is Normalization. This is the process of eliminating any redundant data from your tables. This will help keep your database neat, and only store information that is relevant to your needs.
发布评论
评论（5）
Barry Williams 发布了一个包含约 600 个数据模型的库，适用于各种应用程序。几乎可以肯定，它将为您的所有子系统提供“十个入门”。访问该库是免费的，因此查看一下。
听起来这是您的组织想要的大型“企业”应用程序，而您似乎是数据库的初学者。如果可能的话，您应该从单个子系统（例如订单）开始，并使其正常工作。不仅构建数据库表，还构建一些框架前端。一旦足够好，添加另一个相关的子系统，例如计费。你不想最终成为一个蔓延的怪物。
还要确保您有一个像样的数据建模工具。 SQL Power Architect 对于免费工具来说已经足够好了。
Barry Williams has published a library of about six hundred data models for all sorts of applications. Almost certainly it will give you a "starter for ten" for all your subsystems. Access to this library is free so check it out.
It sounds like this is a big "enterprise-y" application your organisation wants, and you seem to be a bit of a beginner with databases. If at all possible you should start with a single sub-system - say, Orders - and get that working. Not just the database tables build but some skeleton front-end for it. Once that is good enough add another, related sub-system such as Billing. You don't want to end up with a sprawling monster.
Also make sure you have a decent data modelling tool. SQL Power Architect is nice enough for a free tool.
在开始阅读标准化之前，直到您对此完全没有疑问为止。如果你只在学校做过这件事，那么你可能还不够了解它，无法进行设计。
仔细收集您对每个模块的要求。您需要了解：
业务规则（特定于应用程序并且必须在数据库中强制执行，因为无论来源如何，它们都必须对所有记录强制执行），
是否存在法律或监管问题（例如 HIPAA 或 Sarbanes-Oxley 要求） ）
安全性（数据需要加密吗？）
您需要存储什么数据以及为什么（这些数据在其他地方是否可用）
哪些数据只有一行数据，哪些数据需要多行？
您打算如何强制每个表中行的唯一性？您有自然键还是需要代理键（在几乎所有情况下都建议使用代理键）？
需要复制吗？
需要审核吗？
数据如何输入数据库？它是来自应用程序一次一条记录（甚至来自多个应用程序），还是其中一些来自 ETL 工具或另一个数据库的批量插入。
您是否需要知道谁输入了记录以及何时输入（这在企业系统中很可能是必要的。您
需要什么样的查找表？当您可以使用查找表并将用户限制为 ？
什么样的数据验证？
您需要知道要创建多大的测试数据
您需要 procs 或 ORM 或动态查询？
在设计中要记住一些非常基本的事情。不要在字符串字段中存储要进行数学运算的日期或数字。对于数学（零件号、邮政编码、电话号码等）作为字符串数据，因为您可能需要前导零，因此不要在字段中存储多条信息。相关表），当您发现自己在做诸如phone1、phone2、phone 3之类的事情时，请立即停止并设计一个相关表。出于数据完整性目的，请务必使用外键。
在整个设计过程中都要考虑数据完整性。没有完整性的数据是没有意义和无用的。进行性能设计，这在数据库设计中至关重要，而不是过早的优化。数据库不容易重构，因此第一次就获得性能方程中最关键的部分是很重要的。事实上，所有数据库都需要针对数据完整性、性能和安全性进行设计。
不要害怕有多个连接，正确索引这些将表现得很好。不要尝试将所有内容放入实体值类型表中。尽可能少用这些。尝试学会从处理数据集的角度思考，这将有助于您的设计。数据库经过优化可以成组执行操作。
还有更多，但这足以开始消化。
Before you start read up on normalization until you have no questions about it at all. If you only did this in school, you probably don't know enough about it to design yet.
Gather your requirements for each module carefully. You need to know:
Business rules (which are specific to applications and which must be enforced in the database because they must be enforced on all records no matter the source),
Are there legal or regulatory concerns (HIPAA for instance or Sarbanes-Oxley requirements)
security (does data need to be encrypted?)
What data do you need to store and why (is this data available anywhere else)
Which pieces of data will only have one row of data and which will need to have multiple rows?
How do you intend to enforce uniqueness of the the row in each table? Do you have a natural key or do you need a surrogate key (suggest a surrogate key in almost all cases)?
Do you need replication?
Do you need auditing?
How is the data going to be entered into the database? Will it come from the application one record at a time (or even from multiple applications)or will some of it come from bulk inserts from an ETL tool or from another database.
Do you need to know who entered the record and when (highly likely this will be necessary in an enterprise system.
What kind of lookup tables will you need? Data entry is much more accurate when you can use lookup tables and restrict the users to the values.
What kind of data validation do you need?
Roughly how many records will the system have? You need to have an idea to know how big to create your test data.
How are you going to query the data? Will you be using stored procs or an ORM or dynamic queries?
Some very basic things to remember in your design. Choose the right data type for your data. Do not store dates or numbers you intend to do math on in string fields. Do store numbers that are not candidates for math (part numbers, zip codes, phone numbers, etc) as string data as you may need leading zeros. Do not store more than one piece of information in a field. So no comma-concatenated lists (these indicate the need for a related table) and while you are at it if you find yourself doing something like phone1, phone2, phone 3, stop right away and design a related table. Do use foreign keys for data integrity purposes.
All the way through your design consider data integrity. Data that has no integrity is meaningless and useless. Do design for performance, this is critical in database design and is NOT premature optimization. Database do not refactor easily, so it is important to get the most critical parts of the performance equation right the first time. In fact all databases need to be designed for data integrity, performance and security.
Do not be afraid to have multiple joins, properly indexed these will perform just fine. Do not try to put everything into an entity value type table. Use these as sparingly as possible. Try to learn to think in terms of handling sets of data, it will help your design. Databases are optimized to do things in sets.
There's more but this is enough to start digesting.
尝试在这里将您的担忧分开。用户能够更新数据库更多的是一个“应用程序设计”问题。如果你的数据库设计正确，那么就应该为它开发一个漂亮的前端。
首先要查看的是标准化。这是从表中消除任何冗余数据的过程。这将有助于保持数据库整洁，并且仅存储与您的需求相关的信息。
Try to keep your concerns separate here. Users being able to update the database is more of an "application design" problem. If you get your database design right then it should be a case of developing a nice front end for it.
First thing to look at is Normalization. This is the process of eliminating any redundant data from your tables. This will help keep your database neat, and only store information that is relevant to your needs.
数据模型资源书。
http://www.amazon.com/Data-Model-Resource-Book-Vol/ dp/0471380237/ref=dp_cp_ob_b_title_0
沉重的东西，但非常好。总共 3 卷...
有很多很好的通用结构 - 但它们并不容易，因为它们涵盖了所有内容；）不过，这始终是一个很好的起点。
The Data Model Resource Book.
http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237/ref=dp_cp_ob_b_title_0
HEAVY stuff, but very well through out. 3 volumes all in all...
Has a lot of very well through out generic structures - but they are NOT easy, as they cover everything ;) Always a good starting point, though.
数据库不应该是模型。它用于在工作会话之间保存信息。
您不应该在数据模型上构建应用程序，而应该在遵循业务逻辑的良好面向对象模型上构建应用程序。
完成对象模型后，请考虑如何保存和加载它以及随之而来的所有数据库设计。
（但显然你的公司只是想让你设计一个数据库？而不是一个应用程序？）
The database should not be the model. It is used to save informations between sessions of work.
You should not build your application upon a data model, but upon a good object oriented model that follows business logic.
Once your object model is done, then think about how you can save and load it, with all the database design that goes with it.
(but apparently your company just want you to design a database ? not an application ?)