Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2008
    Posts
    4

    Question Complex Multirelational DB Design

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  3. #3
    Join Date
    Jul 2007
    Posts
    96
    Quote Originally Posted by TStone
    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.
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Diabolic
    PS: If you are having problems with the table design prepare to have issues with the query design
    quote of the day
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2008
    Posts
    4
    Quote Originally Posted by Diabolic
    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

    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.

  6. #6
    Join Date
    Jan 2008
    Posts
    4
    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


  7. DB Layout:
    • Models
    • MB_ID
    • MB_Name
    • Manufacturers.Manufacturer_ID


  • Manufacturers
  • Manufacturer_ID
  • Manufacturer_Name






Last edited by TStone; 01-18-08 at 12:59.
Reply With Quote Reply With Quote

  • #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by TStone
    • Table
    • Fields
  • 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    Reply With Quote Reply With Quote

  • #8
    Join Date
    Jan 2008
    Posts
    4
    Quote Originally Posted by r937
    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. :-)

  • #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally Posted by r937
    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
    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?

  • #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  • #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally Posted by r397
    Originally Posted by mike_bike_kite
    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?

  • #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  • #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally Posted by r397
    Originally Posted by mike_bike_kite
    Is my design wrong?

    in this case i would say no
    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.

  • #14
    Join Date
    Jul 2007
    Posts
    96
    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 =)

  • #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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.
    George
    Home | Blog

  • Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •