I have been working with MSSQL DBs for a few years as part of my duties to support an application that is very DB dependant. I have designed a handful of DBs, but none of them have been as complex as what I am now attempting. Earlier today I broke out Excel to visualize the tables I want to create and my brain froze. I am here to ask for some help.

My end goal is to create a database that allows anyone to search for motherboards by specifications. An example search may be for a MB from a specific manufacturer that has Three or more PCI slots, 1 or more PCI Express 1x slots, 1 AGP slot, four memory banks, supports up to 4GB of RAM, supports DDR2 RAM (yada yada) and any combination of those things either included or excluded from your search that you are interested in.

At first I was planning to build one table full of unique manufactuers, one table full of unique slot types, one table full of unique processor types, etc. The problem comes in when I start thinking about how to relate all of that information back to one record set. One motherboard can not only support more than one type of slot, but they very often support multiples of identical slot types. Some even support multiple different types of memory or multiple different types of processors (I can go on and on).

so my question is... how in the world do I relate all of this information together while trying to maintain some decent level of normalization? The more I think about it the more I think I'm just going to have a LOT of duplicate fields in a relational tables where there many be entries with multiple fields that are identical and maybe only one field is different.

Anyhow, any feedback anyone can give will be greatly appreciated. I'm hoping I'm just making this a lot more complicated than it needs to be.

Relax, you are making it more complicated than necessary.

This smells a lot like homework to me, so I'll give you a hint. You need a motherboard entity, a strong entity for each category of information you want to track (such as memory, IO slot, etc), and a weak entity with a quantity attribute to link the motherboard to each of the strong entities.

-PatP

Normalization, normalization, normalization. With a solid table design you won't have any problems acheiving your goal. Just follow pat's hint.

For free I can give you another: don't use excel to design tables, at least use SQL Server Diagrams.

PS: If you are having problems with the table design prepare to have issues with the query design

Let us know how's it going

quote of the day

I did make it pretty far last night with a design using only Excel, but I'll check that other app out too.

As long as I know where the data is the queries are easy the easy part... mostly because, right or wrong, I'm thinking in terms of queries during the design phase. It's figuring out how to organize the data while limiting redundancy that is tricky, but I'm definately making progress.

Actually, here's what I have so far. It's incomplete because I may have to make further considerations depending on the type of spec, but this seems to fit so far. I'm open to suggestions.

Primer:

• Table

• Fields

DB Layout:

• Models

• MB_ID
• MB_Name
• Manufacturers.Manufacturer_ID

• Manufacturers

• Manufacturer_ID
• Manufacturer_Name

• Specs

• Spec_ID
• Spec_Name
• Spectypes.Spectype_ID
• Spec_Description

• Spectypes (Expansion Card Slot, CPU Socket, Memory Type, Form Factor, etc.)

• Spectype_ID
• Spectype_Name

• MBSpec_Index

• Models.MB_ID
• Specs.SPEC_ID
• Quantity

please tell me you are not going to have meta-data in your application database

tip: any time you store either a table name or a column name in your own database, you are almost certainly making a design error

LOL... no.... no...

That first section was just a primer so you understood what I was explaining below that. I edited the post to try to make that more clear. :-)

I'm currently polishing off a program that analyses (sybase) databases and reports on the various performance issues etc. Almost every table within this application's database has fields to hold table names and field names! Have I made mistake?

probably not, since your application is about tables and columns

I'm also currently doing a reporting system for a Japanese bank. Their existing system dumps reporting data to 100's of tables which are then used to generate various fixed reports. I was planning on building a system that will store the names of the reporting tables and also a chained series of candidate fields that could be used to total by and drill down through (assuming a field is available). This would give them customised web based reporting and take only minimal effort. Obviously I'd be storing table names and candidate field names in the small database that serves this application.

The system shouldn't take long to write and aims to provide a bunch of much needed new functionality while utilising all the work that they currently have in place. Is my design wrong?

in this case i would say no, it is remedial design on top of a crappy database

you are spoiling to get involved in another EAV argument, mike, and i'm not going to bite

let me rephrase my tip: any time you store either a table name or a column name in your own database, you are almost certainly making a design error (unless you are comfortable with EAV-type designs, see nothing wrong with them, and do not understand the value of this tip to the general population of developers)

I shall breath a huge sigh of relief then !

It was just that I found your original statement (tip: any time you store either a table name or a column name in your own database, you are almost certainly making a design error) to be a little too sweeping - I'll agree it may well be inadvisable for most to venture down this route as it's easy to make mistakes, and small mistakes on these types of systems tend to multiply their effects outwards. Similarly if things are done well then I feel that the benefits also multiply out. I guess it just depends what you're comfortable with.

TStone, dispite the fact that I dislike your naming conventions I think you should be ready to start with that model. Just don't forget to enforce the propper FKs and Contraints =)

There's an exception to every rule; but it's up to the student to find that out on their own. You can only teach so much; so teach the majority answer. If you get the foundations right these exceptions will be solvable.