Solved

constructing a table(s) in SQL 2012

Posted on 2013-12-02
11
136 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

749 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