Solved

constructing a table(s) in SQL 2012

Posted on 2013-12-02
11
135 Views
Last Modified: 2013-12-09
I have to get some advice again in constructing a table for my SQL database.

I need to list various Agencies.  Each agency can have multiple Goals.  Each goal can have multiple Objectives.  Each Objective can have multiple Performance Indicators.  Each Performance Indicator has a measure

Here is the beginning of my data list:

Agency
Goal
Objective
Performance Indicator
Measure

Should I have 3 separate tables with an AgencyID in each one?  Does that mean I will have to inner Join my tables each time when selecting, updating and inserting them?  Does that affect sql performance?

Need some advice.  Btw, can you have too many tables in a database.  I already have close to 30 tables.  Some are big.
0
Comment
Question by:al4629740
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 39690602
>Should I have 3 separate tables with an AgencyID in each one?
Nope nope nope nope nope.

>Each agency can have multiple Goals.
So the Goal table will have a primary key GoalID, with a foreign key AgencyID, such that there can be multiple rows for the same agency with different goals.

>Each goal can have multiple Objectives.
So the Objective table will have a primary key ObjectiveID, with a foreign key GoalID, such that there can be multiple rows for the same goal with different objectives.

>Each Objective can have multiple Performance Indicators.
So ... would the Performance Indicators (say Unemployment rate) be the unique for each Objective, or not?

If unique...
So the PerformanceIndicator table will have a primary key PerformanceIndicatorID, with a foreign key Objective, such that there can be multiple rows for the same Objective with different PerformanceIndicator.

If not unique...
PerformanceIndicatorID is unique, and will not have a foreign key.
The Objective table has a foreign key PerformanceIndicatorID.

>Btw, can you have too many tables in a database.  I already have close to 30 tables.  Some are big.
30 is not a problem.  How many rows are we talking for each table? Guessing still not a problem for what you're trying to pull off.
0
 
LVL 33

Assisted Solution

by:Big Monty
Big Monty earned 167 total points
ID: 39690608
30 tables is not that many (I work on web apps that have close to 100 tables. you can read this article to get a sense of maximum specs in sql server

i think jimhorns answer hits it spot on
0
 

Author Comment

by:al4629740
ID: 39690614
On the article it says "Limited by number of objects in a database"  

What does that mean?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 33

Expert Comment

by:Big Monty
ID: 39690618
tables are considered database objects, as well as stored procedures, views, functions, users, etc....
0
 

Author Comment

by:al4629740
ID: 39690621
Does that mean I will be using Inner Join statements all the time when setting them up in this manner with separate tables?

For example.  If I setup the database as stated above, how would I select and agency named ACME and list all their Goals, Objectives and Performance Indicators?
0
 

Author Comment

by:al4629740
ID: 39690624
Is there a limited number of objects any given database can have?
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39690635
>how would I select and agency named ACME and list all their Goals, Objectives and Performance Indicators?

The original question here spoke very well to design.
This question speaks to requirements .. how do your users wish to view their data?  Although  yes, to utilize multiple tables in a single SELECT statement they need to be joined by their related columns.   This has no bearing on 'number of objects'.
0
 
LVL 33

Assisted Solution

by:Big Monty
Big Monty earned 167 total points
ID: 39690643
not sure of the exact number, but I think its in the tens of thousands range. You'll most likely run out of disk space before you hit the max number
0
 

Author Comment

by:al4629740
ID: 39690648
Yes the objects question was unrelated...

They will need to view the data in a datagrid within VB6 in a grid format.  It would nice for them to be able to see the data as a "spreadsheet" format.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39690733
>I have to get some advice again in constructing a table for my SQL database.
>"Limited by number of objects in a database"  What does that mean?
>They will need to view the data in a datagrid within VB6 in a grid format.

Looks like this question has been answered, and is starting to go off in multiple directions.
If you have a new question, can we stick to the subject at hand in this one, and have you ask it as a new EE question?

>They will need to view the data in a datagrid within VB6 in a grid format.
Has anyone met with 'They' and come up with requrements on exactly how this should look?
So far this is nothing the above SQL design can't handle, and if you want to pursue that it would be better asked as a new question in the VB Classic zone.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 83 total points
ID: 39691317
>> Should I have 3 separate tables with an AgencyID in each one? <<

Based on what you've described so far, you will end up with at least 4 different tables.

But, before you get to the physical design with "tables" and "columns", you really should do a logical design, with "entity types" (often called just "entities"), "attributes" and "relationships" (at least; a few other things are possible).

You don't necessary need to worry about a particular diagramming method to show the Entity Types, Relationships, etc., just sketch them out first for clarity.


"Agency" is an entity for sure.

"Goal" is less clear.  Can a Goal be set outside the context of an Agency?
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question