Normalizing OS Information
Hi folks,
I'd like your input on the best way to normalize OS information as part of a server information database that I'm creating.
P_Servers PK is SN; and it is a given that each OS (Windows Server) has a Version (NT - 2003, R2), an Edition (Standard, Enterprise, Data Center, etc), and a Service Pack (varies by Version).
How would you approach this? I have a basic data model idea, but it has been a while since I took that course!
Thanks
[490 byte] By [
Sturdy] at [2007-11-11 10:28:34]
# 1 Re: Normalizing OS Information
How do you intend to query the information? Will you need, for example, to find all servers that do not have a specific service pack installed? If you don't need that level of granularity, you may simply create a different record for each version/edition/SP combination, e.g.:
1. Windows NT 4.0 Standard Edition
2. Windows NT 4.0 Standard Edition, SP1
3. Windows Server 2003 Standard Edition
4. Windows Server 2003 Enterprise Edition
5. Windows Server 2003 Enterprise Edition, SP1
...etc.
If you need to track service packs, you could break them out into a separate table. Similarly, if you need to track Editions, they could be in their own table, but I'm not sure what the benefit of that would be.
# 2 Re: Normalizing OS Information
Good questions. The primary use of this database is more informational (pulling info from too many random spreadsheets and databases) but as we talk more about the potential for other people to access its information, the scope is starting to show broader possibilities. In the future, it could be used to query all servers with certain editions and/or service packs.
I suppose even with all of the OS version/edition/sp information in one column, a query could still be done...but in my understanding, this would be pretty far from 3NF. With over 900 servers to track, 3NF seems like a worthwhile goal, no?
Sturdy at 2007-11-11 23:44:25 >
# 3 Re: Normalizing OS Information
3NF is a lot like your high school transcript: it becomes a lot less important after you finish school. ;-) Seriously, it's a nice goal, but it may be overkill if you don't need that level of granularity.
The primary advantages of normalization are query performance, space savings, and ability to change a value in a single location. So, if you want to be able to query on service pack (e.g., "Which servers need to have SP3 applied?"), that's a good argument for storing SPs in a separate table. But you won't save much space (if any) by storing the characters "SP1," "SP2," etc. in a separate table. And how likely is it that you'll want to change those values?
On the other hand, if you can store OS version and edition in a single table, your queries will be much simpler. You need to figure out what you gain in exchange for the added complexity, and if it's worth it.
