0

I've tried looking around the internet for an answer, but because I don't understand databases very well I'm having trouble phrasing my question to work as a Google search.

I apologize if this has been answered somewhere, but I can't seem to find it. A link to something that could answer this would be greatly appreciated.

I am trying to set up tables in my database. I have a Software table that contains many fields, including a field called "Compatible with."

"Compatible with" is used to store the operating systems this software is compatible with (i.e. Windows XP, Windows 7, etc.). Simple enough.

With other fields that have a set number of responses (like dropdownslists), I have normalized the database with separate tables and foreign keys. My guess is that it would be "good practice" to do this as well for the "Compatible with" field, but I'm not really sure how I should set up the normalized table.

I found something here http://forums.asp.net/t/1675666.aspx/1 that may be the right direction to go, so I thought of making my table like this:

Column Name          Data Type
CompatibleWithId     int
Windows XP           bit
Windows 7            bit

...want to be able to add more later...

The problem I ran into is that in the future I will need to add to the list of options in the "Compatible with" field. Is there a way in C# code (I am using MVC 2 and Entity Framework 4.3.1 I believe) to add a column to a table like the one above? Or is there a completely different way I should be setting this up?

Travis Schreier
  • 65
  • 6

1 Answers1

1

According to my understanding of your software requirement (please correct me if i am wrong), here is how I will have the database schema

table1: software (id, name, blah, blah)
table2: operating_sys (id, os_name, version)
table3: compatibility (software_id, os_id)

Relations:

Software to compatibility --> one to Many

So compatibility table will have multiple rows for each software. Now in future if you have more Operating systems, or versions you can add them in the operating_sys table, and add a corresponding entry in compatibility table.

Is that what you were looking for?

Shaunak
  • 17,377
  • 5
  • 53
  • 84
  • Yep. That looks like a much better way to do it. Thank you! – Travis Schreier Jul 11 '12 at 15:42
  • Quick question. Want to make sure I got it straight. For the compatibility table, software_id and os_id will be foreign keys to the other 2 tables, right? and there isn't a primary key for the compatibilty table? – Travis Schreier Jul 11 '12 at 15:45
  • yes they are both foreign keys. You may have a primary key 'id' in compatibility table (your database will probably have it anyway), but you wont need it, atleast not for trivial purposes like finding compatible os for a software. its a good idea to have it anyways, just in case in future you need more complex results. – Shaunak Jul 11 '12 at 15:49