新手数据库设计 [英] Novice Database Design

问题描述

大家好吧，好吧，我是一名小学教育老师，试图教这些小青蛙一些读书，写作，和''ritmetic！我肯定是一个新手数据库，但可以看到一个明确的需要有一个适用于这么多领域的技能，特别是在我的学校。香港专业教育学院在这个论坛上得到了一些非常棒的帮助，让我达到了这一点。不能再感谢他们了。它们是宝藏:)


即时通讯使用msaccess 2016并启动数据库来跟踪为低收入人群提供免费和低成本资源的组织。我一直在从他们发送给我的信息中研究表设计，因此数据库从包含个人，组织，可用资源以及申请这些资源和某些关系所需文档的数据的表开始。数据绝不是真实的实体。


所以，我已经包含了.accdb的zip文件。我只是想知道最初的设计是否适合继续进行，或者如果我站在周围没有袜子和我的鞋子在错误的脚上。哈哈


欢呼！

附加文件

（63.2 KB，174 views）

好开始，Molly。


小点，但表名中的下划线并没有真正增加可读性。


我不熟悉城市和城市的情况。国家在美国工作，但我的猜测是每个城市都有自己的州（奥斯汀 - 德克萨斯州，休斯顿德克萨斯州，明尼阿波利斯 - 明尼苏达州等）


如果这是正确的，你需要一个yourTblCities中的StateID，所以一旦你选择了城市，状态就是预定义的。


以类似的方式，我会在地址表中有一个带有CityID的地址的表。基本上你已经部分得到了，但是你的两个表中都有地址ID tblOrg，tblInds。


这将简化整个过程，因为在你构建了你的状态表之后，当你添加一个新的城市时，你只需要使用一个组合框来选择正确的状态。


当你建立你的城市表时，当你添加一个新的地址，再一个组合框来挑选正确的城市。


再一次，不熟悉美国地址，就是一个足够的地址线。当然，在我的一个数据库中，我需要3行+ CityID&邮政编码（房屋名称，街道名称，村庄）


我附上图片。我称之为郡，你称之为国家，我称之为城市，你称之为城市。但我的猜测是，它们是相同的。

这是会员表的地址部分，您将看到用于选择的组合框镇（城市）

希望这可能会让你有个好的开始


Phil

hi all

well, im an elementary education teacher trying to teach these little frogs some readin'', writin'', and ''ritmetic! im a novice databaser for sure, but can see a definite need to have a skill thats applicable to so many areas, especially in my school. ive had some fantastic help from the pros on this forum to get me to this point. cant thank them enough. they are treasures :)

im using msaccess 2016 and starting a database to track organizations which provide free and low cost resources for individuals with low incomes. i have been studying table design from information they sent to me, and so the db begins with tables which contain data on individuals, organizations, available resources, and documentation needed to apply for those resources and some relationships. the data are in no way real life entities.

so, ive included a zip file of the .accdb. im just wondering if the initial design seems appropriate to carry on or if im standing around with no socks and my shoes on the wrong feet. haha

cheers!

Attached Files

Bytes.zip (51.9 KB, 13 views)

解决方案

Those little ''uns are going to be shocked at your lack of the use of capitalisation for proper nouns ;-)

After a very quick check all seems reasonable. I can at least confirm it can be opened in 2010.

Can you let me know if you''ve received my PM?

As always - good luck.

Fairly brief look at the data and the table design
[tbl]_orgs and [tbl_inds] :
- if you want to keep names and addresses togeither then personally I would do something like
(in the following I will be using [PK] == primary key and [FK] == foreign key)

[PK][FirstName][LastName][BusinessName][...remaining address fields...]
If you might want to setup a means of preventing duplicated [firstname][lastname] pairs and [businessname]

- What I might do would be
[t_states]
as you currently have and use it

[t_Cities]
as you currently have and use it

[t_OrgsAndIndv]
[PK][FirstName][LastName][BusinessName]
Composite index no duplicate [FirstName][LastName]
Index no duplicate [BusinessName]
(might compost index all three in one index... I''ll have to think about that)

[t_StreetAdress]
[PK][address fields]

[t_PhoneNumbers]
[PK][TelephoneNumbers]

[t_PhoneType]
[PK][PhoneClass]
(Home/primary)(Mobile)(ICE)(VMOnly)...

[t_OrgAndIndvAddrssProfile]
[PK][FK_OrgsAndIndv][FK_StreetAdress][SendMailHere]
composite key [FK_OrgsAndIndv][FK_StreetAdress] no duplicates add required at table
This will allow multiple address per organization/individual

[t_OrgAndIndvPhoneProfile]
[PK][FK_OrgsAndIndv][FK_PhoneNumbers][FK_PhoneType]
composite key on all three [FK] no duplicates add required at table
This will allow multiple telephones per organization/individual

>> Personal thought
I really dislike composite primary keys, such as in [brg_Orgs_Resources] in that when one starts to SQL-Joins and VBA these tend to get messy; HOWEVER, the composite index allows for one to prevent duplications within M:M (many to many) join table. There have been MAJOR conversations here on Bytes, other forums, and even at the University level.

Here''s my take on these - if I am not going to be pulling a lot of data via VBA or pushing/pulling via SQL then I will use a composite key as the primary. If I will be manipulating/accessing the data a lot (especially in VBA) then I''ll use an autonumber [PK] and use a composite index set to prevent duplications.
***KEEP IN MIND*** I am not a database developer by training - just by necessity.


OK - this was very brief so take with a HUGE grain of salt - I have to go help the wife cook supper for the heathens!

Attached Images

Molly00Mayhem_971105_OEM_Relationships.JPG (63.2 KB, 174 views)

Good start, Molly.

Small point, but the underscores in the Table Names don''t really add to the readability.

I am not familiar with how cities & states work in the US, but my guess is that each city has its own state (Austin-Texas, Houston Texas, Minneapolis-Minnesota etc)

If that is correct, you need a StateID in yourTblCities, so once you have selected the city, the state is predefined.

In a similar way, I would have a single table for addresses with a CityID in the Address table. Basically you have partially got that, but then you have AddressID in your two tables tblOrg, tblInds.

That will simplify the whole procedure because, after you have built ub your table of States, when you add a new City, you just need to use a Combo Box to pick the correct state.

Same thing applies when you have built up your table of Cities, when you add a new address, again a Combo box to pick the correct city.

Again, not being familiar with US addresses, is one address line enough. Certainly in one of my databases I need 3 lines + CityID & Zip Code (House Name, Street Name, Village)

I attach a picture. What I call a County, you call a State, and what I call a Town you call a City. but my guess is that they are identical.

This is the address portion of a membership form, and you will see the Combo box for selecting the Town (City)

Hope that may get you off to a good start

Phil

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