表设计 - 一个还是多个？ [英] Table Design- one or many?
问题描述
我刚刚开始处理部分完成的项目。它是
是一个使用访问存储测试结果的应用程序。测试
结果存储在两个Access 2000数据库中。
DB＃1 = StudentDB
DB＃2 = TestResulstsDB
为什么有2个dbs？我不知道 - 但这是我们将要改变的价格之一。将所有数据合并到一个数据库中。
但目前的设计是我所关注的。我绝不是一个
专家 - 我也没有接受过任何关于编程或数据库设计的正式培训。
如果问题是基本的，请原谅我。
我觉得我有很好的数据库设计，所以当我意识到
在这里发生时 - 我有点担心。
好像我上面提到的那样有一个学生数据库和一个TestResults数据库
（一个数据库中没有两个表 - 但是有两个数据库）
当学生参加考试时，结果存储在TestResults DB中的动态
构建表中。
因此，如果学生进行5次测试，则有5个相应的表用于
这个学生 - 每个考试一个。
几个问题 - 评论/关注
＃1我的直觉告诉当我试图检索测试结果时，这个设计将是一个绝对的噩梦。我不在这里吗？
＃2有些测试每次测试将有600条记录，需要存储
。因此，包含许多测试结果的单个表可以快速增长。
每个记录包含四个数字数据字段。这不是一个网页
应用程序 - 这是一个独立的应用程序，它将是这台机器上运行的唯一应用程序
。可能还有一台联网的机器。
一张桌子中的多条记录会有多少条记录？
＃3将有多套不同的测试结果。它是否有利于每组结果设计一个表，或者一个表带有
所有结果？
每个测试类型只有一些这些字段会有数据 -
在结果中留下许多与测试无关的空值
。
testresults1 testresults2
fieldA number fieldE int
fieldB number fieldF varchar（35）
fieldC number fieldG number
fieldD number
或
testresults
fieldA number
fieldB number
fieldC number
fieldD number
fieldE int
fieldF varchar（35）
fieldG number
提前感谢您的帮助。
Kim
请回复新闻组..
***通过开发人员指南 http： //万维网。 developersdex.com ***
不要只是参加USENET ......获得奖励！
<<为什么有2个dbs？我不知道 - 但这是我们
将会改变的事情之一。 >>
我不建议你这样做，除非你仔细检查了为什么这样做了
一开始就是这样。我的猜测是，其他一些更大的系统会产生学生数据库，因为学生每年都在变化。旧的
格言是不要修复一些不会破坏的东西。
-
PC数据表
您的资源以获取Access，Excel和Word应用程序的帮助
re ****** @ pcdatasheet.com
www.pcdatasheet.com
"基米" < ki*@kimmyXSPAMX.com>在消息中写道
news：40 *********************** @ news.frii.net ...我刚开始研究一个部分完成的项目。它是一个使用访问存储测试结果的应用程序。测试结果存储在两个Access 2000数据库中。
DB＃1 = StudentDB
DB＃2 = TestResulstsDB
为什么有2个DBS？我不知道 - 但这是我们将要改变的事情之一。将所有数据合并到一个数据库中。
但目前的设计是我所关注的。我绝不是一名专家 - 我也没有接受过任何关于编程或数据库设计的正式培训。
如果问题是基本的，请原谅我。
我觉得我有良好的数据库设计，所以当我意识到这里发生了什么时 - 我有点担心。
好像我上面提到的那样有一个学生数据库和一个TestResults DB
（不是两个表在一个数据库中 - 而是两个数据库）
当学生参加测试时，结果存储在TestResults DB中的动态构建表中。
因此，如果学生进行5次测试，那么该学生将有5个相应的表格 - 每个测试一个。
几个问题 - 评论/关注
＃1我的本能告诉我，当试图检索测试结果时，这种设计将是一场绝对的噩梦。我不在这里吗？
＃2有些测试每个测试将有600条记录需要存储。因此，具有许多测试结果的单个表可以快速增长。
每个记录由四个数字数据字段组成。这不是一个web
应用程序 - 这是一个独立的应用程序，它将是在这台机器上运行的唯一应用程序。可能还有一台联网机器。
＃3会有多组测试结果不同。是否有利于每组结果设计一个表或一个表具有
所有结果？
每个测试类型的位置只有一些字段会有数据 -
离开结果中的许多空值与测试中的测试无关。
testresults1 testresults2
fieldA number fieldE int
fieldB number fieldF varchar（35）
fieldC号码字段号码
fieldD号码
或者
testresults
fieldA号码
fieldB号码
fieldC号码
fieldD number
fieldE int
fieldF varchar（35）
fieldG number
提前感谢您的帮助。
Kim
请回复新闻组..
***通过Developersdex发送 http://www.developersdex.com ***
不要只是参加USENET ...奖励它！
<<<<为什么有2个dbs？我不知道 - 但这是其中一个
我们将会改变b $ b。 >>
<<我不建议你这样做，除非你已经检查过<<<<<<>为什么
它是完成
<<开头的那种方式。我的猜测是，其他一些<<更大的系统
生成了
<<<学生数据库，因为学生从<<<年变为<。 br />
旧的
<< adage不会修复一些不会破坏的东西。
你好谢谢你的回复。
也许我对这个问题的回答有点轻浮 - 而且它不是b / b
的意思。在完成任何设计更改之前，我将完全理解整个当前设计 -
。我的工作是重新设计
数据库。应用程序正在开发中，需要一些
重新设计来处理新的数据库设计。
请回复新闻组..
***通过开发人员指南发送 http：// www.developersdex.com ***
不要只参加USENET ......获得奖励！
"基米"写了
DB＃1 = StudentDB
DB＃2 = TestResulstsDB
为什么有2个dbs？我不知道 -
但这是我们将要改变的事情之一。将所有数据合并到一个数据库中。
但目前的设计是我所关注的。我绝不是一名专家 - 我也没有接受过任何关于编程或数据库设计的正式培训。
如果问题是基本的，请原谅我。
我觉得我有良好的数据库设计，所以当我意识到这里发生了什么时 - 我有点担心。
好像我上面提到的那样有一个学生数据库和一个TestResults DB
（不是两个表在一个数据库中 - 而是两个数据库）
当学生参加测试时，结果存储在TestResults DB中的动态构建表中。
因此，如果学生进行5次测试，那么该学生将有5个相应的表格 - 每个测试一个。
几个问题 - 评论/关注
＃1我的本能告诉我，这个设计在尝试检索测试结果时将是一个绝对的噩梦。我在这里吗？
你绝对正确。每个学生每次测试创建多个表格是比你需要的任何噩梦更糟糕的事情
来检索信息并用它做一些事情 - 使用tablename本身而不是数据的经典案例
。
＃2有些测试将有600条记录
每次测试需要存储。因此，具有许多测试结果的单表可以快速增长。每条记录包含四个数字数据字段。这不是一个web
应用程序 - 这是一个独立的应用程序，它将是这台机器上运行的唯一应用程序。
可能还有一台联网机器。
多少条记录将在一张桌子上的许多记录？
很多人在这里报告非常可接受的表现，有数百个b
数千或数百万的记录。
＃3会有多个使用不同的结果集进行测试。是否有利于为每组结果设计一个表或一个包含所有结果的表？
对于每种测试类型，只有部分字段会有数据 - 离开结果中的许多
空值与相关测试无关。
我认为我们没有足够的信息回答这个问题。在
一般情况下，最好只有一张桌子，但具体
有关您保存的信息以及如何使用它的详细信息
可能表示针对特定情况的不同设计。
Larry Linson
Microsoft Access MVP
I have just started working on a project that is partially complete. It
is an application that is using access to store test results. The test
results are being stored in two Access 2000 databases.
DB #1 = StudentDB
DB #2 = TestResulstsDB
Why are there 2 dbs? I do not know - but that is one of the tings that
we will be changing. Combining all of the data into one database.
But the current design is what I am concerned about. By no means am I an
expert - nor have I had any formal training in Programming or DB Design.
So pardon me if the question is elementary.
I feel I have a decent handle on good db design so when I realized what
was going on here - I was a bit concerned.
Ok like I mentioned above there is a Students DB and a TestResults DB
(not two tables in one databases - but two databases )
When a student takes a test the results are stored in a dynamically
built table in TestResults DB.
So if the student takes 5 tests there are 5 corresponding tables for
this student - one for each test.
Several questions - comments/concerns
#1 My instincts tell me this design will be an absolute nightmare when
trying to retrieve test results. Am I off base here?
#2 Some of the tests will have 600 records per test that will need to be
stored. So a single table with many test results could grow quickly.
Each record consists of four fields of numeric data. This is not a web
app - this is a standalone application and it will be the only app
running on this machine. With maybe one other networked machine.
How many records will be to many records in one table?
#3 There will be multiple tests with different sets of results. Would it
be beneficial to design one table per set of results or one table with
all results?
Where for each test type only some of the fields will have data -
leaving many null values in the results that are not related to the test
in question.
testresults1 testresults2
fieldA number fieldE int
fieldB number fieldF varchar(35)
fieldC number fieldG number
fieldD number
OR
testresults
fieldA number
fieldB number
fieldC number
fieldD number
fieldE int
fieldF varchar(35)
fieldG number
Thank you in advance for your help.
Kim
Please Reply to the Newsgroups..
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!解决方案<< Why are there 2 dbs? I do not know - but that is one of the tings that we
will be changing. >>
I don''t recommend you do this unless you have checked closely on why it was done
that way in the beginning. My guess is that some other bigger system generates
the student database because the students change from year to year. The old
adage is don''t fix something that ain''t broken.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"kimi" <ki*@kimmyXSPAMX.com> wrote in message
news:40***********************@news.frii.net...I have just started working on a project that is partially complete. It
is an application that is using access to store test results. The test
results are being stored in two Access 2000 databases.
DB #1 = StudentDB
DB #2 = TestResulstsDB
Why are there 2 dbs? I do not know - but that is one of the tings that
we will be changing. Combining all of the data into one database.
But the current design is what I am concerned about. By no means am I an
expert - nor have I had any formal training in Programming or DB Design.
So pardon me if the question is elementary.
I feel I have a decent handle on good db design so when I realized what
was going on here - I was a bit concerned.
Ok like I mentioned above there is a Students DB and a TestResults DB
(not two tables in one databases - but two databases )
When a student takes a test the results are stored in a dynamically
built table in TestResults DB.
So if the student takes 5 tests there are 5 corresponding tables for
this student - one for each test.
Several questions - comments/concerns
#1 My instincts tell me this design will be an absolute nightmare when
trying to retrieve test results. Am I off base here?
#2 Some of the tests will have 600 records per test that will need to be
stored. So a single table with many test results could grow quickly.
Each record consists of four fields of numeric data. This is not a web
app - this is a standalone application and it will be the only app
running on this machine. With maybe one other networked machine.
How many records will be to many records in one table?
#3 There will be multiple tests with different sets of results. Would it
be beneficial to design one table per set of results or one table with
all results?
Where for each test type only some of the fields will have data -
leaving many null values in the results that are not related to the test
in question.
testresults1 testresults2
fieldA number fieldE int
fieldB number fieldF varchar(35)
fieldC number fieldG number
fieldD number
OR
testresults
fieldA number
fieldB number
fieldC number
fieldD number
fieldE int
fieldF varchar(35)
fieldG number
Thank you in advance for your help.
Kim
Please Reply to the Newsgroups..
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
<<<< Why are there 2 dbs? I do not know - but that is one of the tings
that we
will be changing. >>
<<I don''t recommend you do this unless you have checked <<closely on why
it was done
<<that way in the beginning. My guess is that some other <<bigger system
generates
<<the student database because the students change from <<year to year.
The old
<<adage is don''t fix something that ain''t broken.
Hello Thank you for replying.
Perhaps my answer to this questions was a tad flippant - and it was not
meant to be. I will completely understand the entire current design -
before any design changes are made. It is my job to re-design the
database(s). The app(s) areall in development and will required some
re-design to handle the new db(s) design.
Please Reply to the Newsgroups..
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
"kimi" wrote
DB #1 = StudentDB
DB #2 = TestResulstsDB
Why are there 2 dbs? I do not know -
but that is one of the tings that we will
be changing. Combining all of the data
into one database.
But the current design is what I am concerned about. By no means am I an
expert - nor have I had any formal training in Programming or DB Design.
So pardon me if the question is elementary.
I feel I have a decent handle on good db design so when I realized what
was going on here - I was a bit concerned.
Ok like I mentioned above there is a Students DB and a TestResults DB
(not two tables in one databases - but two databases )
When a student takes a test the results are stored in a dynamically
built table in TestResults DB.
So if the student takes 5 tests there are 5 corresponding tables for
this student - one for each test.
Several questions - comments/concerns
#1 My instincts tell me this design will
be an absolute nightmare when trying
to retrieve test results. Am I off base here?
You are absolutely correct. Creating multiple tables per test per student is
going to make it worse than any nightmare you''ve ever imagined when you need
to retrieve the information and do something with it -- a classic case of
using the tablename itself instead of data.
#2 Some of the tests will have 600 records
per test that will need to be stored. So a
single table with many test results could
grow quickly. Each record consists of four
fields of numeric data. This is not a web
app - this is a standalone application and it
will be the only app running on this machine.
With maybe one other networked machine.
How many records will be to many records
in one table?
Many people report here very acceptable performance with hundreds of
thousands or millions of records.
#3 There will be multiple tests with
different sets of results. Would it
be beneficial to design one table per
set of results or one table with all results?
Where for each test type only some of
the fields will have data - leaving many
null values in the results that are not related
to the test in question.
I don''t think we have enough information to answer this question. In
general, it would probably be best to have just one table, but specific
details about the information you are keeping and how you will be using it
could indicate a different design for the specific situation.
Larry Linson
Microsoft Access MVP
这篇关于表设计 - 一个还是多个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
