Solved

constructing a table(s) in SQL 2012

Posted on 2013-12-02
11
124 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 32

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
 
LVL 32

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
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.

 

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 32

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:ScottPletcher
ScottPletcher 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now