Results 1 to 8 of 8

Thread: Design Advice

  1. #1
    Join Date
    Nov 2002
    Posts
    9

    Unanswered: Design Advice

    HI there my first post so be gentle...

    I am redeveloping a web store which is an ASP based site and am looking to make life easier for myself and other staff at the company I work for as well as our customers. I have some problems with my existing data, it is becoming quite a task to manage and this boils down to receiving product information from Vendors in varying formats and in some cases partial data from two different vendors to make one catalogue.

    When the site was originally developed it was designed for one "master" product table. We currently still use this and use catalogue numbers and barcodes as our keys to pulling the various supporting information out of our Vendor databases this works OK but we now have many duplicates but fixing the table is not an option as too much of the internal systems rely on this master table for looking up product information.

    I have a plan which I am currently in the middle of conceiving, and hence asking for advice here, to re-code our website to directly browse and search purely on the Vendor databases, this way our customers can view the absolute best up to date catalogues and if we need to we can perform a complete reload of the data if we wish most importantly not affecting the other databases or our internal systems. To keep track of what the customers order I intend to add the important product information - such as price, supplier etc to an orders table. Our staff can then use this information to process the orders regardless of the status of my product data that is web facing. Now strictly speaking I dont think this design is correct, as I will be duplicating quite a bit of data into that already exists in my Vendor databases into my orders table and certain problems will arise that I can forsee already like having to write specific code for the browsing and searching of each Vendor database but I think the overall benefits outweigh the current setup. we use SQL server 2k and have several million rows of existing data that will need importing into the new structure aswell. What do you think of doing this i.e. keeping the product data and order data totally seperate and developing the website around this so essentially the add to shopping cart button on the website is what does the adding of data between the two.

    phew long post...any advice appreciated!

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Are these vendor databases you are referring to - are they part of your system or are they the actual remote databases of your vendors ? And how does the "master" relate to these ?

  3. #3
    Join Date
    Nov 2002
    Posts
    9
    They make up the product range that we sell from the website and are supplied by the vendors as databases (normally dbf or csv format - but they will usually supply a schema). I will take these databases and provide facility to browse, search and order the products on the website.

    As the rest of our system is designed to access a single table for the products, my code on the website takes the information from the Vendor databases and as the customers browse our site & add products to their cart some background code adds records into the master product table so that we can keep track of what they have in their cart.

    This solution is not particularly suitable as it creates a lot of duplicate entries (if two different customers order the same product for example or our staff may delete a record for whatever reason and add a new record.)

    what I am thinking is to eliminate the master table, and seperate my data into two totally different entities - product data and order data. I doubt this is good design but Its the easiest way I can see of simplifying the system.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    So are the customers browsing the "databases" that the vendors supply to you - you don't import this data into a central products table ?

    Why not import the vendor's databases into 1 (use can use dts to help you transform the data into a standard if possible)- rather than waiting on a customer to pick - this would be easier to manage and faster ? Next, use the "true master" as a query only - use the unique key from this and use it to populate the order details table for the customer.

  5. #5
    Join Date
    Nov 2002
    Posts
    9
    this would be the ideal however the vendor databases are very different in product information, change frequently (daily/weekly change,additions & deletes) the vendor data also contains "related" product information that isn't directly related to orders that would logically not work in a single product table. My "keys" to pull ou t the related data i need would be barcodes and/or catalogue numbers but these are a) not always unique or b) do not match up (so we lose valuable product information that would generate sales)

    My Vendor databases could potentially number 5 - 10 and range from single table 50mb files to 10gb relational structures. Getting it all to work off of a single product table with no dupes and allowing staff to make changes aswell as the daily/weekly updates just spins my head.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    To be honest (and I have very minimal information for this recommendation) I would re-examine the entire process and redesign my structure. The problem you will continue to have is "patching" the process to make everything work based on a design that may have worked in the beginning but is beginning to show it flaws. There are several techniques to handle disjointed data sources that you have while still having a centralized repository of data (usually keys that just point to the actual table/databases that you need to retrieve data). Basically, creating an intermediate table to abstract the complexity of the data sources beneath it, while allowing it to be manageable and maintainable.

    There are times that you are much better off punting the existing design - this may have an upfront cost that seems too expensive but realize that in a very short time the maintainability will pay for itself - any customization on both the database and software development side will be minimal. Plus adding additional vendors to this design would be seamless.

  7. #7
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb

    RE: To be honest (and I have very minimal information for this recommendation) I would re-examine the entire process and redesign my structure. The problem you will continue to have is "patching" the process to make everything work based on a design that may have worked in the beginning but is beginning to show it flaws. There are several techniques to handle disjointed data sources that you have while still having a centralized repository of data (usually keys that just point to the actual table/databases that you need to retrieve data). Basically, creating an intermediate table to abstract the complexity of the data sources beneath it, while allowing it to be manageable and maintainable.
    There are times that you are much better off punting the existing design - this may have an upfront cost that seems too expensive but realize that in a very short time the maintainability will pay for itself - any customization on both the database and software development side will be minimal. Plus adding additional vendors to this design would be seamless.
    RE: [I would re-examine the entire process and redesign my structure.]

    S1 I agree, and support the sentiment.

    S2 Create a sound logical design that addresses the business needs. This is probably the best advice you can take. Since your situation appears large and complex, you may want to implement a sound end result in two or more intermediate stages that may more easily be budgeted, implemented, and adjusted for 'unforseen' issues over a period of few quarters or longer (rather than use a 'Big Bang' cutover strategy).

  8. #8
    Join Date
    Nov 2002
    Posts
    9
    Thanks for the replies definitely very useful comments. What I am considering is to totally seperate my product data from my order data and use code on the website to transfer the neccessary data into my order table so the staff can process the customer orders.

    vendor data ---> sql server ---> website ---> sql server ---> order tables ----> order processing

    whereas the current setup is along the lines of

    product tables <---> sql server <---> website
    ^
    |
    order tables

    my data changes so frequently that this is the only simple way I can see of approaching the problem.

    If i go along the route of having keys in a centralised database pointing to the source of the information I think this would be too much effort to manage and ensure it is kept in order.

    Thanks for your help its great to be able to throw some ideas around as I do not currently have a second technical person to discuss this in detail with!

Posting Permissions

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