I am a newbie to the world of SQL Server and I am trying to develop a parts database for computer components. I need tables for each component type (mobo, cpu, memory, hard drives etc.) but I also want to show relation between compatible components (i.e. sata harddrives for mobos that support sata as an example) in my queries and stored views so that if a user selected a Motherboard they can recieve a list of compatible components with that Motherboard. So far I have setup tables for each component, each table has a comp_id (where comp is the component type) and I have a comp_type_id table that stores all of the component id's. Each component table's comp_id column is the primary key relating to the comp_type_id table (which has the foreign key. Is this the way to go or am I completly out of the ball park. Any suggestions or guidence will be greatly apprieciated.I'd recomment NOT creating separate tables for each component. That would be a nightmare to maintain.
Instead, create a single table for storing all components and assigne each component a unique ID. Then have a second table for storing compatibilities with two fields as the primary key: Comp_ID and Compatible_Comp_ID.
This is an ambitious project, and your real challenge will be getting people to enter data religiously. You may think you are making the database more powerful by adding functionality, but you may end up making it such a pain to use and update that the data is never reliable. Just a word to the wise: keep it simple!
blindman|||I see what you mean. I will work that idea out on paper. What kind of index would you use on a table that massive? Also, how do I indicate two primary keys for one table.
Thank you for your suggestions|||You are not assigning two primary keys. You can only have one primary key (the clustered index) because this dictates the order that the data is actually stored in SQL server. Other keys (non-clustered indexes) are stored as separate invisible tables that contain pointers to your actual data. This is why they are slightly slower than clustered indexes in queries, and they also take up additional space.
What you need to do is make the clustered index contain both fields. You can do this easily in Enterprise manager's table designer by selecting both fields and then clicking on the KEY icon.
Some people create a single compound keyfield by concatenating the data from both fields into a third field, but I would recommend against this for a lot of reasons.
As far as the kind of index to use, I like UniqueIdentifers, which are GUIDs (Globally unique IDs). They aren't the shortest type of ID, and they are no fun to type in, but they are efficient and ensure that if you ever have to merge data from multiple databases (production, test, backup, etc) you won't have to worry about duplicate data.
Since you are creating a parts database, I doubt you are going to be dealing with millions of rows of data, so the efficiency of the index is not nearly as important as the functionality and clarity of the design.
blindman
