Linking tables

[mama16]

Hello, I'm trying to build this db with data that resides on spreadsheets.

I currently have 4 tables,

"Deployers"

State_ID PK
Population

"Cities"
State_ID
cities

"Statistics"

State_Id
calls per hour


"Quarter"
state_id
ist
2nd
3rd
4th

Is it ok if I link all four tables by state_id, example AZ, CA, FL,?

In deployers state_id will not repeat but in the others it will. For example, Statistics table has stats for each state from 01/2000 to present

 

[Crowley16]

yes, but you'd need to add a primary key into the tables in which stateID isn't unique...

--------------------
Procrastinate Now!

 

[mama16]

Access won't let me because it will create duplicates, that's if I use state_Id as the primary key. Like I said, state_ID does not repeat in the deployers table but it does in the other tables. Should I create another field like a an auto number field for the other tables

 

[Crowley16]

autoNumber field if no other more suitable fields can be made into a primary key...

personally, I'd choose an exsisting field or a combination of exsisting fields cos it doesn't add redundant data and actually means something...

--------------------
Procrastinate Now!

 

[mama16]

Will an auto number give you redundant data? This is what I'm doing,
"Deployers"

State_ID (PK)
Date_deployed
Months_in_operation
phone_number

will be linked as one to many to "City" cause one state has many cities, example, FL has Tampa, Miami, Orlando

AutoNumber (PK)
State_ID
Cities

"Deployed" will be linked to "Statistics" as a one to many, cause one state will appear many times. This stats are from 01-2000 till present so "FL" will have stats from 01/2000,02/2000,03/2000 and on.... There are about 28 states

"Statistics"
Autonumber(PK)
State_ID
Peak_call_hour
Peak_call_count
Average_call_length

"Statistics" will be linked to "Quarter" as a one to many, one state has many stats from 1st,2nd,3rd,4th quarter

"Quarter"
Autonumber(pk)
state_ID
1st Jan-Mar
2nd Apr-Jun
3rd Jul- Sep
4th Oct-Dec

Will this work ok, or do you think I should make up a field and give it a PK instead of using autonumber.

Thanks for helping me out, I really appreciate it

 

[Crowley16]

autonumber is a field has includes a list of numbers that are automatically generated, in most cases such numbering is only used for indexing, and doesn't relate to the information in any other way, so therefore is redundant.

your strat looks ok, although you could combine statistics and quater into 1 table, so you have:
StateID, startDate, endDate, callHour, callCount, callLength

use startDate and endDate to work out quater

--------------------
Procrastinate Now!

 

[mama16]

The spreadsheet where I have the Quarter data is call "Call total by months"
and the data is like this

1Q 1Q 1Q 2Q ...... 1Q
Jan02 Feb02 Mar02 Apr02 Jan03

AZ 70,414 62,548 70,5262
FL 30,685 45,785 54,145


Can I add those fields to the Statistic table? like

"Statistics"
Autonumber(PK)
State_ID
Peak_call_hour
Peak_call_count
Average_call_length
1st
2nd
3rd
4th

Then, I'll have 3 tables, but again the primary key bothers me.


Again, thanks so much

 

[Crowley16]

you should arrange your tables in such a way that the format makes sense to you, and is as easy to manipulate as possible...

Since you've only got 3/4 tables, don't worry too much about normalisation...

don't worry too much about the PK either, as long as you've got one, you shouldn't run into too much trouble

--------------------
Procrastinate Now!

 

[mama16]

So can I leave auto number as the primary key for two tables like

"Deployers"

State_ID (PK)
Date_deployed
Months_in_operation
phone_number

Link it with state_ID.Statistics as one to many

"Statistics"
Autonumber(PK)
State_ID
Peak_call_hour
Peak_call_count
Average_call_length
1st
2nd
3rd
4th

and

"Deployers"

State_ID (PK)
Date_deployed
Months_in_operation
phone_number

linked with "state_id.city" as one to many

"city"
AutoNumber (PK)
State_ID
Cities

Will that be ok?

Again, appreciate.

 

[mama16]

Also, what if I add this fields to the statistic table instead of just 1st, 2nd, 3rd, 4th.
1st1
1st2
1st3
2nd1
2nd2
2nd3
3rd1
3rd2
3rd3
4th1
4th2
4th3

Will that work?

 

[lespaul]

Even with a small number of tables, you should still abide by the rules of normalization.

Read 'The Fundamentals of Relational Database Design'.

Leslie

 

[mama16]

I'm confused. What's wrong with those fields or should I use
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

and create a year field

the data is like that in Excel

1Q 1Q 1Q ...... 2Q
Jan02 Feb02 Mar02 Apr03

AZ 70,414 62,548 70,5262 45,254
FL 30,685 45,785 54,145 56,5466

 

[lespaul]

I would have

Would store the information you have shown as follows:

StateID
MonthYear
Amount

My records in this case would be:

StateID        MonthYear            Amount
AZ               01/02              70414
AZ               02/02              62548
AZ               03/02              705262
AZ               04/03              45254
FL               01/02              30685
FL               02/02              45785
FL               03/02              54145
FL               04/03              565466

Leslie

 

[mama16]

Thank you so much Leslie,

So I'll have

AZ 01/02 70414
AZ 02/02 62548
AZ 03/02 705262
AZ 04/02 45254
AZ 05/02
AZ 06/02
AZ 07/02
AZ 08/02
AZ 09/02
AZ 10/02
AZ 11/02
AZ 12/02
AZ 01/03 ect.........till present

and its the same for the other states. is that right?
Then, I'll create a field called MonthYear

Thank you again

 

[mama16]

And assign a text data type to it.
Month_Year Text

 

[mama16]

Leslie or whoever,

So this will be fine,

"Deployers"

State_ID (PK)
Date_deployed
Months_in_operation
phone_number

Link it with state_ID.Statistics as a one to many

"Statistics"
Autonumber(PK)
State_ID
cities
Peak_call_hour
Peak_call_count
Average_call_length
Total_Amount_Call
Month/Year

Will that be ok?
Before I had 4 tables, now I have two. I hope this works

Again, appreciate.

 

[lespaul]

Deployers"

State_ID (PK)
Date_deployed
Months_in_operation
phone_number


Is there only one Deployer in each state?
_________________________________________________________


"Statistics"
Autonumber(PK)
State_ID
cities
Peak_call_hour
Peak_call_count
Average_call_length
Total_Amount_Call
Month/Year


You have a field here named CITIES, you do not want to put multiple cities in the same field. So, if you are planning on filling this field with a comma separtated list, then I would suggest breaking this down to another table so that each city has its own information.

So, how does cities fit into the Statistics information?

leslie

 

[mama16]

Thanks again Leslie

The "Deployers" table will list all the states that are using the system. States will not be repeated. I'll show only once. That's why I assigned the primary key to State_ID

"Statistics"
Will have the statistics for all states, but the states are going to repeat because like I mentioned FL 01-2000,
FL 02-2000 .... AZ 06-2003, AZ 07-2003 etc.. so I can not assign a PK to state_ID.statistic. That's why I'm using an auto number as a primary key.

Some states have cities, for example CA, Sacramento, San Francisco. FL, Orlando, Tampa, Miami and this cities have statistics.

First, I had a "City" table but then I got rid of it and created a field called city in the Statistic Table. Does that makes any sense?

 

[mama16]

If I run a query on the Statistic Table like
Give me the states that have cities and also have stats

Select states_id.deployers, cities.statistics, stats.statistics
From deployers, Statistics
where states_id.deployers = states_id.statistics
and states_id.deployers = "FL" and
cities.statistics = "Miami";

Will this work?

 

[mama16]

The Field is City.

Fl Orlando 02/2000 59,2653
AZ 04/2003 60,2564
CA Sacramento 05/2000 70,5896
FL Miami 09/2004 56,256

Is it right?