Solved

constructing a table(s) in SQL 2012

Posted on 2013-12-02
11
131 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

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