473,322 Members | 1,522 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Novice Database Design

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
File Type: zip Bytes.zip (51.9 KB, 59 views)
Jul 23 '18 #1
15 1447
NeoPa
32,556 Expert Mod 16PB
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.
Jul 23 '18 #2
zmbd
5,501 Expert Mod 4TB


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
File Type: jpg Molly00Mayhem_971105_OEM_Relationships.JPG (63.2 KB, 416 views)
Jul 23 '18 #3
PhilOfWalton
1,430 Expert 1GB
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
Jul 24 '18 #4
PhilOfWalton
1,430 Expert 1GB
Sorry zmbd, looks as if our posts crossed.

Poor Molly, two somewhat divergent opinions.

Phil
Jul 24 '18 #5
zmbd
5,501 Expert Mod 4TB
@PhilOfWalton

Small point, but the underscores in the Table Names don't really add to the readability.
Matter of preference - no? I do this, mainly a hold over from when spaces were taboo in all file and table names

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)

For USA address one could either have the states joined to the cities or joined at the address table
[t_cities]
[pk][CityName][FK_State]
composite index [CityName][FK_State] no duplicate
(or even a M:M[City][State])
Not sure if there's a drawback with this method, just a lot of entries for example "Greenville" could have potentially 50 entries as it's found in every US state - or Spring Field would have something like 40ish entries as it's found in most of the US states.

or the same relationship is achieved
[t_address]
[pk][address fields][FK_City][FK_State]
Composite key here dpending on the no duplicate needs...

USA: States/Counties/(Cities/village/towns) we rarely need more than: [Sent to Name] [Street Address 1] [Street Address 2][City][State][ZipCode]
Jul 24 '18 #6
hi neopa

those little 'uns are shock proof i swear! you know, i dont know how to reply. last time i tried i got a msg that seemed to imply that i had used up my 3 replies, but for the life of me i cant remember sending any.

anyway, when i go to my inbox i have a whole bunch of pms. if i open one i can see the msg. if i click on the reply button the msg appears in a textbox with 2 buttons underneath: preview message and submit message, which to me is odd because how can i preview or submit a msg i havent yet typed? i suppose i could have clicked on one, but then i didnt want to goof things up like i have with duplicate posts and not choosing best answers and some edited posts. i get confused just handling my google emails. oh boy! haha so...

cheers!
Jul 24 '18 #7
zmbd
5,501 Expert Mod 4TB
molly00mayhem
In the PM editor, when you [Reply] it automatically quotes the message you are replying to; hence, the [Preview] option is available from the start as is the [submit]
[Preview] lets you see what you've done before you send it
[Submit] sends the message
cheers
-z
Jul 24 '18 #8
hi philofwalton

yeoowww!! boy did you say it. i rarely use timeout with my kids, but with you boys ill make an exception. put your toys down and your hands on your desks for 30 seconds <lol>.

i get the general drift of what youre both saying. let me think about this and get back with you.

cheers!
Jul 24 '18 #9
hi zmbd

thanx for rearranging my relationships. yours shows the relationships much better. all the replies have good points and trying to keep things as manageable as possible for me at my stage and yet have an eye out for starting to learn best practices is a bit of a nailbiter, but i want to eventually facilitate code since i know thats where im headed.

i see the problem with duplicate names for Inds and Orgs. in fact, it turns out that some of the Orgs have branch offices in other cities. so, instead of combining all sorts of comments at random, i decided to change my db first along your recommendations and see how it turns out. i did it only for the Inds at this point. i also changed the relationships.

for the composite indexes, i noticed that i could not choose "no duplicates" for fields individually but had to create indexes. i fumbled around and finally got a query to show the results and it looks nice. only problem is i cant add or delete records in it.

so, im going to upload the revised version if you have time to look at it. hope i didnt mess up on what you suggested :)

cheers!
Attached Files
File Type: zip Bytes.zip (78.6 KB, 39 views)
Jul 24 '18 #10
PhilOfWalton
1,430 Expert 1GB
@zmbd

Thanks for clearing up US addresses. I take your point about the same City name in a number of States, but where this doesn't occur (and don't forget we are dealing with probably a few hundred addresses at the most). I suspect the StateID being a FK in the tblCities is easier. When the city is selected from the Combo box, the State will show in the second visible column.

@ Molly
Small point, but earliet you mentioned Organisations as well as individual "contributors". You can use the same address table for both, so I would be inclined to rename it tblAddresses (remove the ind bit)

Phil
Jul 24 '18 #11
hi philofwalton

well, horace the owner is outside wheeling that little lawnmower of his around like hes hunting for gold in the ground. i notice hes keeping an eye on my rosebush tho haha he knows what will happen if one of my babies says ouch!

many thanx for your suggestions. now i want to do a version of my db with your suggestions and see what happens. this city/state thing is more important than i originally thought. not only are theyre duplicate city names, but i got onto mapquest and discovered that even street addresses are duplicated, right down to the same spelling.

so, im going to combine the tblInds addresses and the tblOrgs addresses all into one table and include StateID in TblCities and then have CityID as a foreign key in the addresses table. i can see how this would play out in your form.

im going to take horace a big glass of sun tea and see how things are going :)

cheers!
Jul 24 '18 #12
PhilOfWalton
1,430 Expert 1GB
Sounds good.

Below is the same picture as Address.Jpg, but with the Combo Box dropped down.



As you see even if there are duplicate Townns (Cities), their County (State) and even Country are all visible.

When the combo box is collapsed you only see the town.

If, on the other hand you want to see City & State use a list box



Phil
Jul 24 '18 #13
hi philofwalton

yes, so many ways to do everything! i notice all the null values in the listbox. i got the same thing when i combined my organization addresses and my individual addresses into one table. is it ok to have so many nulls? some of the articles ive read seemed to imply that tables should generally have the majority of the fields with data.

cheers!
Jul 25 '18 #14
PhilOfWalton
1,430 Expert 1GB
Hi Molly

Not sure what you mean by "So many Nulls". Can you send an image of where you have got this problem.

Also, it might just be worth sending an image of your updated relationships just to check they are OK.

Even send the Db if you wish,

Cheers

Phil
Jul 25 '18 #15
hi philofwalton

well, i finally got the db the way i want it, or rather the way the staff at the food bank want it. should have been more in contact with them. well start working with it tomorrow. thank all of you for the help youve given me getting started the right way. thats what gramma used to say were gonna get you started right so youll turn out right. i guess they did allright.

cheers!
Jul 27 '18 #16

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Lefevre, Steven | last post by:
Hey folks - Thanks to everyone who gave input to my concerns. Of course, we don't intend to have the mysql port open to the world. We will have Apache/PHP connect on a unix socket, or to another...
3
by: Rushikesh | last post by:
I am designing a WEB BASED Accounting Software with ASP and SQL Server. For this I need some help for the Database design. My design is as follows. I)User table: User_id, UserName..... Users...
5
by: Don Vaillancourt | last post by:
Hello all, Over the years as I design more database schemas the more I come up with patterns in database design. The more patterns I recognize the more I want to try to design some kind of...
1
by: hhhhhhhhh | last post by:
I am looking for a recommendation for an introductory database design book which will enable me to design my first database. It should cover the basics such as normalization and entity-relationship...
1
by: Lane Beneke | last post by:
All, New to the list and a relative newbie to PostgreSQL. Please forgive stupid questions. Designing an application server for a work order processing (et al) database. I have a good handle...
2
by: John C | last post by:
I am trying to develop a access database version 2002 from scratch and I am a novice programmer and need much direction. I have been researching and studying about relational database design and...
3
by: cassandra.flowers | last post by:
I'm designing a database because I have to do it for the preperation work for my A-Level ICT exam. The database is for a building company. It has to store information on building projects...
12
by: nyathancha | last post by:
Hi, I have a question regarding best practices in database design. In a relational database, is it wise/necessary to sometimes create tables that are not related to other tables through a...
3
by: vicky | last post by:
Hi All, Can u please suggest me some books for relational database design or database modelling(Knowledgeable yet simple) i.e. from which we could learn database relationships(one to many,many to...
7
by: Jonathan Wood | last post by:
I've been given a spec that seems unwieldy to me. As I'm pretty new to DB design, I would appreciated any input this spec. The site has three types of users: ADMIN, RESELLERS, and CLIENTS. The...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.