试图创建设计良好的数据库 [英] Attempting to create well-designed database

问题描述

在律师事务所工作。试图创建结构坚固的数据库

结构。这是包含字段和主键的表的列表。任何

评论/建议非常感谢。

tbl-被告

CaseNumber（主键）

FirstName

MiddleName

姓氏

DOB

比赛

性别

tbl-JunctionChargesToDefendantsCase

CaseNumber

ChargeID

tbl-收费

ChargeID（自动编号分配主键）

法规

描述

ChargeType

tbl-ChargeTypes（用作表格中的组合框）

ChargeTypeID（自动编号分配主键）

市政

县

州

联邦

tbl-Prosecutor（用作表格中的组合框）

BarNumber（主键）

检察官名称

tbl-外观

AppearanceID（自动编号分配主键）

CaseNumber

外观类型

日期

时间

检察官名称

优惠

辩诉

处置

tbl-AppearanceTypes（在表格中用作组合框）

AppearanceTypeID（自动编号分配主键）

FirstAppearance

提审

DocketSounding

PleaDay

试用


备注：被告可能有一个以上的案件编号，但案件编号

只能有一名被告。


案件编号可能有一次以上的费用而且可以有一笔费用

很多情况。


一个案例有一套结构化的外观类型：


首次亮相
提审

Docket Sounding

辩诉日

试用


由于种类繁多原因，一个案例可能有多个这些

外观类型。

Work in legal office. Trying to create solid designed database
structure. This is list of tables w/fields and primary keys. Any
comments/advice greatly appreciated.
tbl-Defendants
CaseNumber (primary key)
FirstName
MiddleName
LastName
DOB
Race
Gender
tbl-JunctionChargesToDefendantsCase
CaseNumber
ChargeID
tbl-Charges
ChargeID (auto-number assigned Primary Key)
Statute
Description
ChargeType
tbl-ChargeTypes (used as combo box in form)
ChargeTypeID (auto-number assigned Primary Key)
Municipal
County
State
Federal
tbl-Prosecutor (used as combo box in form)
BarNumber (Primary Key)
ProsecutorName
tbl-Appearance
AppearanceID (auto-number assigned Primary Key)
CaseNumber
AppearanceType
Date
Time
ProsecutorName
Offer
Plea
Disposition
tbl-AppearanceTypes (used as combo box in form)
AppearanceTypeID (auto-number assigned Primary Key)
FirstAppearance
Arraignment
DocketSounding
PleaDay
Trial

Notes: Defendants may have more than one case number but case number
can only have one defendant.

Case numbers may can have more than one charge and a charge can have
many cases.

A case has a structured set of appearence types:

First Appearance
Arraignment
Docket Sounding
Plea Day
Trial

Due to various reasons, a case may have more than one of these
appearance types.

推荐答案

fl ****** @ bellsouth.net 写道：

在法律办公室工作。试图创建扎实设计的数据库结构。这是包含字段和主键的表的列表。任何
评论/建议非常感谢。

Work in legal office. Trying to create solid designed database
structure. This is list of tables w/fields and primary keys. Any
comments/advice greatly appreciated.

< snip table description>


第一眼：


我看不到Case表。在我看来Case将是这样一个模型中的主要实体

。


案件编号不是被告的属性。案例编号是缺失案例实体的

属性。


假设被告是惯常类型。你的模型会在每次他/她的b $ b出现在系统中时重复被告的个人信息。

<snip table descriptions>

A first glance:

I see no Case table. Seems to me that Case would be a primary entity
in such a model.

Case number is not an attribute of a Defendant. Case number is an
attribute of the missing Case entity.

Suppose the defendant is the habitual type. Your model would
repeat the defendants personal information each time he/she
showed up in the system.

我建议你使用自动编号为PK。如果有人犯了错误，

会将之前分配的案例编号分配给新客户，那么CaseNumber不会更加独特。自动编号永远是唯一的。


我的业务是为客户提供Access的帮助资源，

Excel和Word。如果您需要有关数据库的帮助，请通过以下我的

电子邮件地址与我联系。


-

PC数据表

您的资源以获取Access，Excel和Word应用程序的帮助
re ****** @ pcdatasheet.com
www.pcdatasheet.com

< fl ****** @ bellsouth.net>在消息中写道

news：11 ********************** @ c13g2000cwb.googlegr oups.com ...

I suggest you use an autonumber for the PK. If someone makes a mistake an
assigns a previously assigned case number to a new client, CaseNumber is no
longer unique. An autonumber will always be unique.

I am in business to provide customers with a resource for help with Access,
Excel and Word. If you need help with your database, lease contact me at my
email address below.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
<fl******@bellsouth.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

在法律办公室工作。试图创建扎实设计的数据库结构。这是包含字段和主键的表的列表。任何
评论/建议非常感谢。

tbl-被告
CaseNumber（主键）
FirstName
MiddleName
LastName
DOB
种族
性别

tbl-JunctionChargesToDefendantsCase
CaseNumber
ChargeID

tbl-Charges
ChargeID（自动） -number分配主键
法规
描述
ChargeType

tbl-ChargeTypes（用作表格中的组合框）
ChargeTypeID（分配的自动编号）主键）
市县
县
联邦

tbl-检察官（用作形式的组合框）
BarNumber（主键） ）
ProsecutorName

tbl-外观
AppearanceID（自动编号分配主键）
CaseNumber
外观类型
日期
时间
ProsecutorName
提供
请求
处理

tbl-AppearanceTypes（在表单中用作组合框）
AppearanceTypeID（自动编号分配为主要键）
FirstAppearance
提交
DocketSounding
PleaDay
试用

注意：被告可能有多个案件编号，但案件编号
只能有一个被告。

案件编号可能有多次收费而且收费可能很多。

一个案例有一套结构化的外观类型：

第一次出场
提审
Docket Sounding
辩护日


由于种种原因，案件可能超过其中一种外观类型。

Work in legal office. Trying to create solid designed database
structure. This is list of tables w/fields and primary keys. Any
comments/advice greatly appreciated.
tbl-Defendants
CaseNumber (primary key)
FirstName
MiddleName
LastName
DOB
Race
Gender
tbl-JunctionChargesToDefendantsCase
CaseNumber
ChargeID
tbl-Charges
ChargeID (auto-number assigned Primary Key)
Statute
Description
ChargeType
tbl-ChargeTypes (used as combo box in form)
ChargeTypeID (auto-number assigned Primary Key)
Municipal
County
State
Federal
tbl-Prosecutor (used as combo box in form)
BarNumber (Primary Key)
ProsecutorName
tbl-Appearance
AppearanceID (auto-number assigned Primary Key)
CaseNumber
AppearanceType
Date
Time
ProsecutorName
Offer
Plea
Disposition
tbl-AppearanceTypes (used as combo box in form)
AppearanceTypeID (auto-number assigned Primary Key)
FirstAppearance
Arraignment
DocketSounding
PleaDay
Trial

Notes: Defendants may have more than one case number but case number
can only have one defendant.

Case numbers may can have more than one charge and a charge can have
many cases.

A case has a structured set of appearence types:

First Appearance
Arraignment
Docket Sounding
Plea Day
Trial

Due to various reasons, a case may have more than one of these
appearance types.

我认为这是一个检察官办公室？

其他一些您可能需要考虑的项目：


被告

JudgeName

DefenseAttorney

TypeOfTrial（判断或陪审团）

OffenseDate

目击者

被告和证人的地址/电话信息

SentencingGuidelinesApply

MotionForDiscoveryFiled

LabTestResultsRecieved

ReasonForContinuance

问题：


你会用它来准备法庭命令吗？

为证人和被告发出子语言？

您想跟踪法庭日期，还是使用数据来管理文件

内容？


评论：


您需要将被告与案件编号分开。被告

在其他情况下可以（并且将会）成为被告，因此您不希望重新输入信息。一般来说，你的桌子名称应该与你的主键相对应。

你的主要密钥 - 如果你的桌子是''被告''，你的pk不应该是

CaseNumber，但是被告_id


tbl-被告

DefendatID（主键）

FirstName

MiddleName

LastName

后缀（Jr，Sr，III）

DOB

比赛

性别


尽管使用BarNumber作为pk似乎是合乎逻辑的，但我不会。

你永远不会知道什么时候会发生奇怪的事情 - 我使用autonumbers
每个表的
，即使pk似乎很明显。此外，如果你在一个有三个以上检察官的办公室，我会考虑存储最后的

名字，名字等而不仅仅是ProsecutorName。随着营业额和特别检察官的b
，这个名单可能比你想象的要长。


tbl-检察官

ProsecuterID（主键） ）

BarNumber

姓氏

FirstName

MiddleName

后缀


市政，县，州和联邦都是收费类型 - 所以这是数据，而不是字段。


tbl-ChargeType

ChargeTypeID（自动编号分配主键）

ChargeTypeDesc（市政，县，州和联邦）


同样的事情。


tbl-AppearanceType（用作表格中的组合框）

AppearanceTypeID（自动编号分配主键）

AppearanceTypeDesc（FirstAppearance，Arraignment，DocketSounding，PleaDay，

Trial）

选择字段名称时要非常小心，不要选择通用名称

会让你适合以后。在tblAppearance中，Date和和时间都是

访问保留字，并且是字段名称的错误选择。这是Tony

Toews'关于命名约定的页面。

http://www.granite.ab.ca/access/tablefieldnaming.htm


我的个人偏好会使像这样的表（表名是

总是单数，不是复数）：


外观

appearance_id（自动编号分配为主键）

app_court_case_id（外键）

app_app_type_id（外键）

app_prosecutor_id（外键）

app_date

app_time

优惠和请求信息应该放在案例表中，而不是

外观表，除非您觉得需要跟踪改变认罪请求


court_case

court_case_id

cc_case_number

cc_offer

cc_plea


你把处置数据放在哪里取决于你的st吃了，但如果它与个人收费保持在同一张表中，那么它将是最好的，而不是与法庭案件相比，b $ b。有些州没有区别，但其他州也没有区别。

在我看来，如果处理

信息与cahrge保持一致，则更容易发现发送错误集中在一起。


我知道法庭案卷号码已经是独一无二的了，那么为什么有一个

单独的自动编号？有几个原因，但这里有两个：

- Docket号码的分配由法院而不是你的办公室完成。

任何时候数据都出来了你的控制，我不愿意使用那个

作为pk - 如果他们在文档编号中犯了错误，后来

的职员想要纠正它？

- 始终使用长数据类型，具有一致的名称（表名+id）

使您在编写代码，调用字段名称等时更轻松br $>
Darryl Kerkeslager

< fl ****** @ bellsouth.net>写道：

I assume this is a prosecutor''s office?
Some other items you may want to consider:

Codefendants
JudgeName
DefenseAttorney
TypeOfTrial(Judge or Jury)
OffenseDate
Witnesses
Address/phone info for Defendants and witnesses
SentencingGuidelinesApply
MotionForDiscoveryFiled
LabTestResultsRecieved
ReasonForContinuance
Questions:

Will you be using this to prepare court orders?
Issue subpeonas for witnesses and defendants?
Do you want to just track court dates, or use the data to manage file
content?

Comments:

You need to separate out defendants from the Case Number. The defendant
can (and will) be a defendant in other cases, so you don''t want to have to
re-enter the information. Generally your table name should correspond with
your primary key - IOW, if your table is ''defendant'', your pk should not be
CaseNumber, but defendant_id

tbl-Defendants
DefendatID (primary key)
FirstName
MiddleName
LastName
Suffix (Jr, Sr, III)
DOB
Race
Gender

As much as it may seem logical to use the BarNumber as the pk, I would not.
You just never know when something weird could happen - I use autonumbers
for every table, even if a pk seems to be apparent. Also, if you''re in an
office with more than three prosecutors, I would consider storing last
names, first names etc rather than just ProsecutorName. With turnover and
special prosecutors, the list may grow longer than you think.

tbl-Prosecutor
ProsecuterID (Primary Key)
BarNumber
LastName
FirstName
MiddleName
Suffix

Municipal, County, State, and Federal all are types of charges - so that is
the data, not fields.

tbl-ChargeType
ChargeTypeID (auto-number assigned Primary Key)
ChargeTypeDesc (Municipal, County, State, and Federal)

Same thing.

tbl-AppearanceType (used as combo box in form)
AppearanceTypeID (auto-number assigned Primary Key)
AppearanceTypeDesc (FirstAppearance, Arraignment, DocketSounding, PleaDay,
Trial)
Be very careful when choosing field names not to choose generic names that
will give you fits later. In tblAppearance, "Date" and "Time" are both
Access reserved words, and are bad choices for field names. Here is Tony
Toews'' page on Naming Conventions.

http://www.granite.ab.ca/access/tablefieldnaming.htm

My personal preference would render the table like this (table names are
always singular, not plural):

appearance
appearance_id (auto-number assigned Primary Key)
app_court_case_id (foreign key)
app_app_type_id (foreign key)
app_prosecutor_id (foreign key)
app_date
app_time
The offer and plea information should go in the Case table, not the
appearance table, unless you feel the need to track a changing plea offer

court_case
court_case_id
cc_case_number
cc_offer
cc_plea

Where you put the disposition data will depend on your state, but it would
be best if it is kept in the same table as the individual charge, rather
than with the court case. Some states don''t differentiate, but others do.
To my mind it is much easier to catch sentnecing errors if the disposition
information is kept with the cahrge, not lumped together.

I know that the court docket number is already unique, so why have a
separate autonumber id? There are several reasons, but here are a two:
- the assigneing of Docket numbers is done by the court, not your office.
Any time that the data is out of your control, I would be loathe to use that
for a pk - what if they made a mistake in the docket number, and later the
clerk wants to correct it?
- always using Long data types, with consistent names (table name + "id")
makes your life easier when writing code, recalling field names, etc
Darryl Kerkeslager
<fl******@bellsouth.net> wrote:

在法律办公室工作。试图创建扎实设计的数据库结构。这是包含字段和主键的表的列表。任何
评论/建议非常感谢。

tbl-被告
CaseNumber（主键）
FirstName
MiddleName
LastName
DOB
种族
性别

tbl-JunctionChargesToDefendantsCase
CaseNumber
ChargeID

tbl-Charges
ChargeID（自动） -number分配主键
法规
描述
ChargeType

tbl-ChargeTypes（用作表格中的组合框）
ChargeTypeID（分配的自动编号）主键）
市县
县
联邦

tbl-检察官（用作形式的组合框）
BarNumber（主键） ）
ProsecutorName

tbl-外观
AppearanceID（自动编号分配主键）
CaseNumber
外观类型
日期
时间
ProsecutorName
提供
请求
处理

tbl-AppearanceTypes（在表单中用作组合框）
AppearanceTypeID（自动编号分配为主要键）
FirstAppearance
提交
DocketSounding
PleaDay
试用

注意：被告可能有多个案件编号，但案件编号
只能有一个被告。

案件编号可能有多次收费而且收费可能很多。

一个案例有一套结构化的外观类型：

第一次出场
提审
Docket Sounding
辩护日


由于种种原因，案件可能超过其中一种外观类型。

Work in legal office. Trying to create solid designed database
structure. This is list of tables w/fields and primary keys. Any
comments/advice greatly appreciated.
tbl-Defendants
CaseNumber (primary key)
FirstName
MiddleName
LastName
DOB
Race
Gender
tbl-JunctionChargesToDefendantsCase
CaseNumber
ChargeID
tbl-Charges
ChargeID (auto-number assigned Primary Key)
Statute
Description
ChargeType
tbl-ChargeTypes (used as combo box in form)
ChargeTypeID (auto-number assigned Primary Key)
Municipal
County
State
Federal
tbl-Prosecutor (used as combo box in form)
BarNumber (Primary Key)
ProsecutorName
tbl-Appearance
AppearanceID (auto-number assigned Primary Key)
CaseNumber
AppearanceType
Date
Time
ProsecutorName
Offer
Plea
Disposition
tbl-AppearanceTypes (used as combo box in form)
AppearanceTypeID (auto-number assigned Primary Key)
FirstAppearance
Arraignment
DocketSounding
PleaDay
Trial

Notes: Defendants may have more than one case number but case number
can only have one defendant.

Case numbers may can have more than one charge and a charge can have
many cases.

A case has a structured set of appearence types:

First Appearance
Arraignment
Docket Sounding
Plea Day
Trial

Due to various reasons, a case may have more than one of these
appearance types.

这篇关于试图创建设计良好的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！