Unsure How to Structure Access Database that Uses Lots of Common Data

[linz862]

Hi All,

I have been struggling with how begin to create an Access Database with my existing data. Currently, I am using an Excel document, but it simply isn't doing the trick.

Explanation of current data:



I am doing a project on O'Charley's Restaurants. I am to check whether or not each of its 220 individual restaurant locations are listed on 20 different online directory sites. These sites include Bing, Yahoo, Google+, Citysearch, Insider Pages, Yelp, etc. The options are that a listing is "correct", "incorrect", or "missing." Each individual O'Charley's Restaurant has a unique store code and each of the 220 location belongs to a specific region (there are 57 regions total). I have obtained all of this different data, but as you can imagine, it is very difficult to calculate across 220 Excel sheets (this is how I've structured my data recordings), as well as figure out for example how many "Correct" listings does all of O'Charley's have on Bing.


For every restaurant the directory sites in question are the same. Additionally, there are only 3 options for a directory site's status (correct, incorrect, or missing) and I would like to be able to break down the data regionally (57 regions) as well by individual location. I am unsure how to create these tables with correct relationships without creating a database that just looks like a gigantic Excel sheet.


PLEASE, PLEASE, PLEASE, any help or guidance would extremely appreciated.


All the best,
Lindsey

[orange]

What have you done so far?
What exactly do you have to deliver to achieve your objective?
Have you identified the steps you need to do?
What triggered the study?
Have you built a data model?
Did you create the 220 spreadsheets?

For a start you will need to research Normalization.
Here is a link to a procedure and sample that leads you through a business issue, teaches Normalization, creates relationships and leads to a properly structured database. I recommend you work through the example to get familiar with the procedure and the terminology.
http://www.rogersaccesslibrary.com/T...lationship.zip

[linz862]

Hi orange,

Thanks for your quick response. So far I have a 220 sheet Excel document with all of the necessary data. I tried to create a Master sheet that included "countif" across all sheets, but I want to take it a step further to be able to express the data in several ways and think Access will allow me to do this. I have tried to create tables in Access, but struggle with correct relationships and table structure.

Here is an example of a single Excel sheet.







I am doing an independent study for school and am trying to figure out the amount of time it takes for a listing on these sites to be updated, as well as recording the accuracy of O'Charley's listings across these sites.

Since every single location is deal with the exact same sites for their listings, I am unsure how to create a correct relationship structure in access. Thank you very much for the Normalization attachment, but I still cannot figure out my dilemma. Any further advice would be much appreciated.


All the best,
Lindsey

[linz862]

For an attribute grid, the following is all the information that I need to be included:

Store Information:
-Store Code
-Address
-City
-State
-Zip
-Region

and

Directory Sites:
-Directory Name
-Directory Status
-Last date I checked the site
-If the site has a duplicate listing

My main struggle is I cannot figure out how to create relationships in Access that relate each individual store location to each individual directory site (but also for ALL 22 directory sites for the single store location). The only structure I can come up with is a Many-to-Many relationship in that:

--Each store has many directory sites.
--Each directory site has many stores.

My rationale for linking and entities is as follows, but I'm not sure how I can have a primary key for "Directory Site" and how each site can have a relationship with "Directory Status"...same goes for "Region" relationship with "Store Information"





I then took this to try and create a db and could not figure out correct structure without making each directory site its own table. It just doesn't seem to to be right.





Any suggestions or guidance would be wonderful.


Thank you so much!
Lindsey

[orange]

Good feedback. Good school project, but I'm wondering about

the amount of time it takes for a listing on these sites to be updated

. If it's O'Charley's that has the wrong info on their specific site, or if it a search engine that hasn't done a re-scan and index of data. If it's O'Charley's site, they can adjust/update. But the search engine/indexer of the various directories may be outside your influence. Just a thought.

I worked on a project a number of years ago, when industry sector specialists had their own databases (black books, local lists, excel/Lotus spreadsheet, dbase, business card file etc). As a government department, we were into corporate database and worked with various branches to create a meaningful online directory(ies) that were current. We found in the branches (about 8 branches) some 50-60 "databases" all purporting to be "gold". Our analysis showed that many were out of date, owner died up to 10 years ago, out of business for 8 years, multiple entries with different names and addresses .....
The revised set up built in 1996 is a http://strategis.ic.gc.ca/cdncc . It is being monitored/expanded/reshaped and adjusted daily. Oh yes, the key to currency was to let and simplify the process for companies to update their own info through government facilities. For companies, and products (and others) there still is a vetting process by specialists. The search engine behind the system itself runs regularly. Links etc on Google, Bing... are really in their ( the search supplier's) control. Enough history.

For your sites directory, you have to step back a little and say What is this ?--What do these values represent?

They are DirectorySites for sure and the attributes are Name, possibly MainURL, and some identifier.
So for the DirectorySite entity I would think

DirectorySite
DirectorySiteID PK autonumber
DirectorySiteName
DirectorySiteMainURL

Region
RegionId PK autonumber
RegionName

Status
StatusId PK autonumber
StatusDate <----This is changed to StatusDescription based on later Post *************

Store
StoreId
StoreAddressLine1
StoreAddressLine2
StoreCity
StoreState
StoreZip
StorePhone
StoreManagerCurrent??

Now the one that's causing the issue. You have many Stores that appear on Many DirectorySites. This is Many to Many
There is a good youtube on this at https://www.youtube.com/watch?v=JBG4SzNhA9A

So, the thing you want to describe is along this line

--Status of the Info about this store at this site on this day.

So let's dream up a name for this thing (entity)--- StoreStatusOnDirectory

StoreStatusOnDirectory

StoreStatusDirectoryID PK autonumber
StoreId (FK) to Store
DirectorySiteID (FK) to DirectorySite
StatusID (FK) to Status
StatusDate
Comment

Note in this entity you would create a compound unique index on this combination of fields
StoreId , DirectorySiteID, StatusID , StatusDate to prevent duplicates.

Research compound unique index.

That should be a good start to help with some of the things as I see/understand them, Review and get back and don't be shy to challenge/correct what has been suggested.

Good luck

[linz862]

Thank you so much for all of your feedback. I do have a question before I try and dive deeper.

In tblStatus, I really need to be able to show the status of the listing: Correct, Incorrect, or Missing. The reason I need this addition is because how I will aggregate the data. For example, I would like to be able to know "How many O'Charley's restaurants have Incorrect listings" and/or "How many of O'Charley's Bing listings are Correct" and also very important "Which Region has the most Correct listings"

This has contributed to much of my struggle since this data has such repetition for each individual store. Each store:
1.) has the same 22 directory listings, but the data is different for each individual store
2.) has the same 3 options for Status (correct, incorrect, or missing), but the Status is only 1 of these choices for each individual listing with 22 different individual listings per each store
3.) belongs to a specific region


Here is what I've come up with. With the "Store_Directory_Status" being a triple key, I will have to manually input all the data and about about 4000 records. Is there a different way to present this triple key? Other than this time consuming step (I also haven't included Regions yet), this structure of relationships seems to be working.





Do you think this will work?


Again, thank you!!
Lindsey

[orange]

I just looked at what I showed for Status.

Status
StatusId PK autonumber
StatusDate

<------------StatusDate was Changed to StatusDescription based on this post

This was incorrect. It should be

Status
StatusId PK autonumber
StatusDescription you could call it StatusName??? <------------StatusDate was Changed to StatusDescription based on this post

so your table values may look like
1,Correct
2, Incorrect
3, Missing and you could add more if you wanted/needed

I don't think you fears are warranted, but I don't know your data.

"How many O'Charley's restaurants have Incorrect listings"
-- Get the latest StatusInfo for each Store
---Count the number Stores where Status = Incorrect


"How many of O'Charley's Bing listings are Correct"
--Get the latest StatusInfo for each Store
--where DirectorySiteName = "Bing"
---Count the number Stores where Status = Correct

"Which Region has the most Correct listings"
--Get the latest StatusInfo for each Store
--Select the Stores where Latest Status is "Correct"
--Select the regions associated with the Stores
--Count(StoreId)


This is just a general approach to answering your questions. I think you have enough data to do so.
Are you missing any data/info/facts to answer your questions?

[linz862]

Seems to being okay thus far with this structure:



The only semi-problem is that the triple primary key in Store_Directory_Status has about 4000 different records. Is this normal, or should it be broken down further? Do you believe this structure will work?


Thanks for all the help!
Lindsey

[orange]

I suggest a Region Table. And on your StoreDirectoryStatus you will need to include a StatusDate, unless there is only 1 status per Store per Site.

I really recommend you have a separate autonumber PK on the StoreDirectoryStatus table and unique compoiste index on the fields mentioned previously. You may prevent some headaches with keys doing it as I have described.