Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    34

    Unanswered: Help -- too much cross-referenced data?

    I have spent a few weeks now, working on a database to save buyer information for a homebuilder. I have the following tables: tblBuyer, tblCommunity, tblModel, tblModelPrice (the price of each model referenced by model and community), tblBroker, etc.

    Now the problem I have is this: my boss wants me to set up a form with a list of options that the buyer can choose for the model of their home.

    Each community has its own set of options. Each model has its own set of options, which also depend upon the community the model is in (some communities have the same models, but with different options and prices). Altogether, there are several hundred possible options, but only 15-60 options per community/model combination.

    It's no big deal to add the fields CommunityID and ModelID to the Buyer table. But with a limit of 255 fields, I don't think it would be wise to add a new Yes/No type field to the Buyer table for every single available option (not to mention that it seems a horrible waste of space).

    Ideally I could create tblOption with all the options on it, and reference them somehow from tblBuyer. Is there an elegant way to do this? Is it possible to combine multiple bits of data into one field? I'm stuck.

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

    Question Re: Help -- too much cross-referenced data?

    Originally posted by Ewin
    I have spent a few weeks now, working on a database to save buyer information for a homebuilder. I have the following tables: tblBuyer, tblCommunity, tblModel, tblModelPrice (the price of each model referenced by model and community), tblBroker, etc.

    Now the problem I have is this: my boss wants me to set up a form with a list of options that the buyer can choose for the model of their home.

    Each community has its own set of options. Each model has its own set of options, which also depend upon the community the model is in (some communities have the same models, but with different options and prices). Altogether, there are several hundred possible options, but only 15-60 options per community/model combination.

    It's no big deal to add the fields CommunityID and ModelID to the Buyer table. But with a limit of 255 fields, I don't think it would be wise to add a new Yes/No type field to the Buyer table for every single available option (not to mention that it seems a horrible waste of space).

    Ideally I could create tblOption with all the options on it, and reference them somehow from tblBuyer. Is there an elegant way to do this? Is it possible to combine multiple bits of data into one field? I'm stuck.
    So the Model options depend on the Community?

    In other words, once you have selected a community, will the options then be restricted for a particular model?

    This could simplify things...I think.

    Gregg

  3. #3
    Join Date
    Oct 2003
    Posts
    34
    Once a particular Buyer chooses a Community, the options get pared down, and then again when the Buyer chooses a Model. (Some options are Community-wide, some are specific to each Model.)

    It does seem like it should simplify things. But I still don't know exactly what I ought to do about it.

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

    Cool

    Originally posted by Ewin
    Once a particular Buyer chooses a Community, the options get pared down, and then again when the Buyer chooses a Model. (Some options are Community-wide, some are specific to each Model.)

    It does seem like it should simplify things. But I still don't know exactly what I ought to do about it.

    I'm thinking......

    I was going to just throw something out since your online at the moment but I need to think it through. I will say that you're looking at at least one linking table to get the information in there.

    I'm still thinking.....

    Gregg

  5. #5
    Join Date
    Oct 2003
    Posts
    34
    I appreciate you taking the time, I really do! I'm going to hash this out with a friend of mine tonight, and if we magically come up with something, I'll post it, but I'm utterly baffled for the moment.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There are a couple possiblities for doing this. I think you're on the right track creating an "options" table. However, I think you're viewing the relationship between options and buyer the hard way.

    In options, I would include only the model ID, option bits, and the community ID. This will allow a seperate entry for each community per model, and also allow you to easily query what the available options for a given model are for any, or all communities.

    Then I would put only the model ID, and the community ID into your buyers table and use those two ID's to reference the corresponding record in options. If the information is populated based on selections from a combo box, you could have the record source for the next form/combo box/report altered fairly simply. Perhaps something like:

    yourform.Recordsource = "SELECT * FROM options WHERE communityID = " & cmbCommunity & " AND modelID = " & cmbModel


    *Editted for continuity.
    Last edited by Teddy; 11-04-03 at 10:29.

  7. #7
    Join Date
    Oct 2003
    Posts
    34
    Since I have options that are community-based only, and options that are based upon model and community combination, I think the easiest way for me to think about it is this: Create a many-to-many relationship between buyers and options, with a table of BuyerID/OptionID combinations.

    I already have the relationships between options, models, and communities figured out, so I don't want to mess with them too much. It may be a slavish dependency on elementary table design, but I'm just starting out learning all of this, still.

    I'm surprised I didn't think of that immediately. I've been pondering this for a few days now... someone else suggested that I code binary sums for option combinations, but I'm not touching that with a ten foot pole, and I don't want to code in concatenated strings of integers and then code them back out again for historical reports.

    Let me know if you think there's anything particularly wrong with my solution, please!

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It still seems to me that you are implenting an unnecessarily complex and archaic design. If you have options based only on community, then I would create a tblComOptions and tblModelOptions. My main reason for taking this approach is future flexibility. If, for instance, you are asked to produce a full listing of communities offering x options on x models, you're going to have a hell of a time doing so without a more flexible structure.

    that's just my 2 cents though, take it for what it's worth.

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

    Smile

    Originally posted by Teddy
    It still seems to me that you are implenting an unnecessarily complex and archaic design. If you have options based only on community, then I would create a tblComOptions and tblModelOptions. My main reason for taking this approach is future flexibility. If, for instance, you are asked to produce a full listing of communities offering x options on x models, you're going to have a hell of a time doing so without a more flexible structure.

    that's just my 2 cents though, take it for what it's worth.
    Teddy:

    Serious question. If all of the options that the community selection will modify are a subset of the options offered by the model chosen, then why would you want a separate table for the ComOps? When I was considering this it was driving me crazy trying to structure the data for this. I'll spare you the details of my thought processes but suffice it to say that it appeared easier than it turned out to be.


    Ewin:

    I really put a bit of thought into this and would appreciate it if you could post or send me a private copy of just the table and relationship structure. No data needed. I was just about sent you what I was working on but saw that you had progressed. If you would prefer not to thats OK, but you never know till you ask.

    Glad it's starting to work for you.

    Gregg

  10. #10
    Join Date
    Oct 2003
    Posts
    34
    I'm already off and running on the next huge problem with this database... if the structure I chose gives me any particular problems, I'll let you know. I need to input some option data and see how it works with the forms I've chosen.

    What I'm trying to do now is open up a form that displays models for a buyer based upon community choice, choice of number of bedrooms, and choice of number of bathrooms... then displays a number of options based upon the model selection, and adds up the price of everything in a box at the bottom.

    So, yeah. Got my hands full.

    Thank you for spending time on this, though... if you came up with something really tight and elegant, I'd still like to hear about it, because this is a temporary job for me. In a few weeks, I'll have time to discuss this further!

Posting Permissions

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