constructing a table(s) in SQL 2012

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.
al4629740Asked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Big MontyConnect With a Mentor Senior Web Developer / CEO of ExchangeTree.org Commented:
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
 
al4629740Author Commented:
On the article it says "Limited by number of objects in a database"  

What does that mean?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
tables are considered database objects, as well as stored procedures, views, functions, users, etc....
0
 
al4629740Author Commented:
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
 
al4629740Author Commented:
Is there a limited number of objects any given database can have?
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Big MontyConnect With a Mentor Senior Web Developer / CEO of ExchangeTree.org Commented:
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
 
al4629740Author Commented:
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
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.