April 13, 2012 at 9:12 am
Hello guys,
This is a newbie question and I apologize from the get go, but Im having a hard time wrapping my head around this idea and for some reason it just doesnt seem right to me...
Here is the background:
I wanted to come up with a documentation database for my company concerning our program development...My idea was simply just going to have one master database and several lookup tables...Lookup tables being Databases, Applications, Servers, and Reports...My view on this is if my manager comes to one of our developers and ask hey what database is this application running off of, and they dont know off the top of their head, they can query this master table and it will show what database is on what server and what applcations/reports are run off that database...
SO Im in the design phase of this and Im thinking of what is the best way to design this database...And I get to Servers and Databases : One server can have many databases, and one database can be across multiple servers ... Classic many to many relationship! So I know you use a "bridge table" to show many to many relationships where the primary key from each table is in the bridge...So as I get to thinking about this: Im wondering ok, in my database table, I have Database 'A'...It is located on Server 1 and Server 2... In my database I will have a DatabaseID that has identity (1,1) property...(this is just an example, not exactly how Im going to populate it):
DatabaseID || DatabaseName || ServerName || ServerID
1 A 1 1
And here is what Im not sure how to show: If I can the second row to show that Database A is also on Server 2 with a ServerID of 2...But if I add another row, it will give it another databaseid so it would look like this:
DatabaseID || DatabaseName || ServerName || ServerID
1 A 1 1
2 A 2 2
But this is not how I want it to look, this shows Database A linked to DatabaseID's of 1 AND 2, where I just want Database A to have Database ID of 1???
How do I design this to show what I am wanting? Any helpful hints will be appreciated! Again I understand this is a newbie question but I have spent most of my short career on the administration side and have limited experience when it comes to the development of databases!
April 13, 2012 at 10:44 am
asm1212 (4/13/2012)
...tables being Databases, Applications, Servers, and Reports...One server can have many databases, and one database can be across multiple servers...DatabaseID || DatabaseName || ServerName || ServerID
From what you've described, it sounds like your data model isn't normalized to your requirements. A typical design for this might look something more like:
table Database:
DatabaseID, DatabaseName
table Server:
ServerID, ServerName
table intersection:
DatabaseID, ServerID, Description
April 13, 2012 at 12:26 pm
Yeah you are correct...
As I was going through it and thinking about it, I was like Why am I putting that information twice? That information is going to be in one table so there is no need to have it in two places!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply