Results 1 to 4 of 4

Thread: Design Question

  1. #1
    Join Date
    Oct 2003
    Posts
    13

    Unanswered: Design Question

    Hi there. I'm having some trouble coming up with an optimal database design pattern here... I could use some input on the best way to go about this. Anyway, here goes...

    The database will contain specifications of certain types of construction equipment. The example I will use is Vibratory Drivers/Extractors (vibros).

    As would make sense, all vibros should be in the same table. HOWEVER, there are differences between certain vibros, and there are similarities for all vibros. All vibros have height, width, length, weight (can differ depending on attachments, clamps, etc, but it's still just weight), and eccentric moment.

    The problem starts here... certain models might need 3 height entries because of different variations. Also, certain models might have an eccentric moment of 5200 in-lbs, while others can be varied i.e. 4400 in-lbs to 5200 in-lbs. So the eccentric moment might be an integer, or a range, or it might need to be some number of set integers.

    To make matters worse, some models have data entries that no other model has, or every model might have a certain thing except for 1 model which doesn't have that specification.

    Can you see my problem yet?

    And now for the next step (what? you thought that was the extent of this? silly you.). All the actual numerical values in the database need to be in US units, but need to be able to be converted to metric. So somewhere I need a table which contains a spec (i.e. "eccentric moment"), what it's US unit is (i.e. in-lbs), what it's metic unit is (i.e. kg-cm), and the value to multiply by for the conversion.

    I'm so frustrated right now (all these guys who design and make these things think making this database will be so simple) that I'm actually considering just making 1 table that contains every possibly field a vibro model could use and blanking out the fields not used, then having another table which contains the names of all those fields, their US unit name, their metric unit name, and the conversion.

    I just know that's not the best way to do it, but I just can't think of a better way.

    If you want some better idea of what the values I'm working with here are, go to http://www.apevibro.com/asp/specs.as...=vibro&model=3 and look through the models listed there. That is the site I'm working on. I took it over from another guy who got fired. It's such a huge mess right now.

    Currently, the database is set up with only 3 colums: ID (contains the model identifier), variable (contains the text of the specification), and value (which contains the value of that spec). None of the value entries are actual numbers either. It contains a text entry that (in the case of a US/metric entry it contains something like "91 cm / 36 in", which is worthless for numerical calculations).
    Alexsha

    I am the Alpha and the Omega...
    the First and the Last...
    The Begining and the End...
    the One who is and who was and who is coming, the Almighty...

    I am root

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Design Question

    Originally posted by Alexsha
    Hi there. I'm having some trouble coming up with an optimal database design pattern here... I could use some input on the best way to go about this. Anyway, here goes...

    The database will contain specifications of certain types of construction equipment. The example I will use is Vibratory Drivers/Extractors (vibros).

    As would make sense, all vibros should be in the same table. HOWEVER, there are differences between certain vibros, and there are similarities for all vibros. All vibros have height, width, length, weight (can differ depending on attachments, clamps, etc, but it's still just weight), and eccentric moment.

    The problem starts here... certain models might need 3 height entries because of different variations. Also, certain models might have an eccentric moment of 5200 in-lbs, while others can be varied i.e. 4400 in-lbs to 5200 in-lbs. So the eccentric moment might be an integer, or a range, or it might need to be some number of set integers.

    To make matters worse, some models have data entries that no other model has, or every model might have a certain thing except for 1 model which doesn't have that specification.

    Can you see my problem yet?

    And now for the next step (what? you thought that was the extent of this? silly you.). All the actual numerical values in the database need to be in US units, but need to be able to be converted to metric. So somewhere I need a table which contains a spec (i.e. "eccentric moment"), what it's US unit is (i.e. in-lbs), what it's metic unit is (i.e. kg-cm), and the value to multiply by for the conversion.

    I'm so frustrated right now (all these guys who design and make these things think making this database will be so simple) that I'm actually considering just making 1 table that contains every possibly field a vibro model could use and blanking out the fields not used, then having another table which contains the names of all those fields, their US unit name, their metric unit name, and the conversion.

    I just know that's not the best way to do it, but I just can't think of a better way.

    If you want some better idea of what the values I'm working with here are, go to http://www.apevibro.com/asp/specs.as...=vibro&model=3 and look through the models listed there. That is the site I'm working on. I took it over from another guy who got fired. It's such a huge mess right now.

    Currently, the database is set up with only 3 colums: ID (contains the model identifier), variable (contains the text of the specification), and value (which contains the value of that spec). None of the value entries are actual numbers either. It contains a text entry that (in the case of a US/metric entry it contains something like "91 cm / 36 in", which is worthless for numerical calculations).
    Sounds like you've got your hands full. My first thoughts are that you are going to need some many to many relationship tables. For instance, a table that ultimately describes the final piece of equipment is going to consist of the basic equipmentID and all the variations but the equipment table will only be for the base equipment and things that don't change about it. I would suggest breaking out as many tables as you can to group the variations. An example would be an attachments table, if for no other reason than to provide a lookup feature when you are building an individual piece of equipment. Then you can create a 1 to many relationship with the Equipment table and also a 1 to many relationship with the attachments since each attachment can be used on lots of equipment models and each equipment model can use lots of attachments. Besides that, the table that describes the individual piece of equipment is going to have a lot of fields. Another thought is that if the final weight can be calculated based on the original weight plus attachments/mods etc then the weight of individual appendages can be stored in the separate tables with the item they pertain to.

    As far as conversions go, these can be calculated and really don't need to be stored separately.

    This is just my first impression and may be off base hey, I tried.

    I'll look to see what other responses you get and chime in as I see more clearly where you're going with this.

    Hope this helps.

    Gregg

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What I might suggest is to break this up into multiple tables ... Take your "eccentric moment" for example: Since it is a range for some models make it a range for ALL models (lowest common denominator here ...) Those models with a single moment will have the same beginning and ending range ... Just test for it. Related to this: Add corresponding metric measures for each range also ... As for converting ... Do you really need to do math? As for the specs, Determine what is common and make those attributes of the equipment and for those specs that are unique or are not defined across each piece of equipment put those in a generic table keyed to the specific piece of equipment ...

  4. #4
    Join Date
    Oct 2003
    Posts
    13
    Thanks for the suggestions... I think I'm going to do a comination solution... 1st: come up with a set of universal attributes common to ALL types and make that the majority of the types. 2nd: Have another table of accessories/attachments. 3rd: Have a set of extra fields for model specific notes.
    Alexsha

    I am the Alpha and the Omega...
    the First and the Last...
    The Begining and the End...
    the One who is and who was and who is coming, the Almighty...

    I am root

Posting Permissions

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