Alpha Video Training
Results 1 to 10 of 10

Thread: Need Help with ERD structuring / Data Modeling for B2B Web Portal

  1. #1
    Member
    Real Name
    Maroine A.
    Join Date
    Apr 2010
    Location
    Rabat, Morocco
    Posts
    122

    Post Need Help with ERD structuring / Data Modeling for B2B Web Portal

    Hello, thanks for reading my post.

    I have included 3 snapshots for the ER diagram and need help ensuring DB integrity and normalization. Your input is very much appreciated, here are the requirements:

    There are 3 main entities: COMPANY, PRODUCT and CATEGORY:

    • When the user fills in its company profile, he/she chooses to link it with up to 5 different categories, and a category can contain many companies, so there is the many to many relationship (* to *) between the entities COMPANY and CATEGORY.


    • The user should be able to add many products linked to his company and one product is linked to only one company so there is the one to many relationship (1 to *) between COMPANY and PRODUCT entities.


    • One product can be in many categories and one category can contain many products, so there is the many to many relationship (* to *) between the entities PRODUCT and CATEGORY.


    • When the user fills in the product information, he can choose to associate that specific product to 1 up to 3 categories, after submit his company is linked to that product in the selected categories.


    The snapshots are self-explanatory and wondering which one is the correct one or close to being right. I have been thinking about this for some time now and can’t figure it out just yet. I would very much appreciate a little push and guidance; this is the backbone of the app and I can’t move forward with the development until this part of the data modeling is designed correctly.

    Thanks
    Attached Images Attached Images

  2. #2
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: Need Help with ERD structuring / Data Modeling for B2B Web Portal

    Nice to see someone putting a design together on paper first.
    A high degree of Normalisation may trip you up when the changes come along - as they surely will.
    Have you tried building any of these examples yet? If not, suggest you do. Then you will see how easy it is to work with the data.
    I will have a go tomorrow, but don't have time today.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  3. #3
    Member
    Real Name
    Maroine A.
    Join Date
    Apr 2010
    Location
    Rabat, Morocco
    Posts
    122

    Default Re: Need Help with ERD structuring / Data Modeling for B2B Web Portal

    Thanks for taking the time to comment. I appreciate it.
    I created many grids and tested many things and got it working but data is redundant not normalized...
    Here is the ERD that is working now
    ERD_4.png
    I just created a button that pops up a linked grid (COMP_CAT for adding categories to companies) and linked those fields IND_ID (industry table) SEC_ID (sector table) and CAT_ID (category table) dynamically with the IND_ID being the parent of SEC_ID and SEC_ID being the parent of CAT_ID and stored their related ids and displayed the names. That way when I will be able to display all the companies in a specific industry or sector or category ... same goes for product listing.
    I got it working but it is redundant ... any thoughts?

  4. #4
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: Need Help with ERD structuring / Data Modeling for B2B Web Portal

    If it's working and easily maintainable - as the ERD suggest it will be, the minor data redundancy won't matter.
    It may make it much easier to generate reports as well.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  5. #5
    Member
    Real Name
    Maroine A.
    Join Date
    Apr 2010
    Location
    Rabat, Morocco
    Posts
    122

    Default Re: Need Help with ERD structuring / Data Modeling for B2B Web Portal

    You may be right: but i am a bit skeptical since the redundacy is negligeable when the DB is small, what would happen when querries are run against millions of records which are constantly increasing? in that case redundacies are much more significant and need much attention...

    Then I asked myself this: Which table will be populated the most or will have the most querries run against? in this example there will be more products than companies since each company can post many products (I also have 1 to 1 hierarchical relationships where the product table is the parent) so i am thinking it'd be less redundant if I remove the redundancies in the PROD_CAT table and link it with the CATEGORY table like this:
    ERD_5.png

    That would work too...Just thinking out loud

  6. #6
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: Need Help with ERD structuring / Data Modeling for B2B Web Portal

    Having worked with highly normalised databases in BT and Gvmnt in the UK, I am sceptical of the Bull Dust surrounding the process.
    Example.
    One Proprietory (£1 million +) SQL app has 2480 tables. Of these, only 47 are used regularly. The application grew like Topsy, and no one dare remove the unused tables as they don't know what will happen. This is as a result of "creeping systems design".
    Another example was at BT. I created an Alpha Database to create an XML Loader because of the normalisation complexities. Took me 10 days, and the VB.Net guy took 4 months and still didn't get his working.

    The simple rules I follow are to Normalise, and then Rationalise.
    Redundant data may be the trade off to enable speedy Queries. Read 1 Table rather than through 4 to get the results.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  7. #7
    Member
    Real Name
    Maroine A.
    Join Date
    Apr 2010
    Location
    Rabat, Morocco
    Posts
    122

    Default Re: Need Help with ERD structuring / Data Modeling for B2B Web Portal

    Thank you Ted for your insight I'll keep that in mind ... you're right, denormantisation can be useful especially in cases where historical records are stored for long period of time.

    Have a good W.E.

  8. #8
    Member
    Real Name
    priyanka
    Join Date
    Mar 2014
    Posts
    1

    Default Re: Need Help with ERD structuring / Data Modeling for B2B Web Portal free advertiment

    : Need Help with ERD structuring / Data Modeling for B2B Web Portal free advertiment

  9. #9
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,291

    Default Re: Need Help with ERD structuring / Data Modeling for B2B Web Portal

    And which ERD development/construction tool are you using?
    I am familiar with Toad and Visual Paradigm, but that is academic.
    What help do you actually need?
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  10. #10
    Member
    Real Name
    Doron
    Join Date
    Dec 2011
    Location
    NJ, USA
    Posts
    173

    Post Re: Need Help with ERD structuring / Data Modeling for B2B Web Portal

    Hello Maroine,

    You are talking about many to many relations and for that we use Linking table. See my article that discusses the many to many relation named: Database Development with Alpha Five v11 and MS SQL Server: Using Linking/Control Tables with some sample data and SQL statements to get you the idea.

    For questions related to data, it could help to see the SQL statement (DDL) and sample data as well to better understand the question.

    Regards,

    Doron
    The Farber Consulting Group, Inc.

    Main Web Site: http://www.dFarber.com
    MS SQL Blog: http://www.dfarber.com/computer-consulting-blog.aspx
    Convert Ms Access to Web
    Custom Software Development
    Alpha Five Development
    No Interest Loans Application
    Last edited by DoronF; 03-25-2014 at 10:46 AM.

Similar Threads

  1. A5 to program a video portal
    By ISCGuido in forum Application Server Version 11 - Web/Browser Applications
    Replies: 4
    Last Post: 01-04-2012, 05:00 PM
  2. What's a FMP Portal in the real world?
    By VictorA in forum Application Server Version 10 - Web/Browser Applications
    Replies: 2
    Last Post: 12-21-2009, 10:45 AM
  3. ERD Software - what are others currently using?
    By Citadel in forum Alpha Five Version 9 - Desktop Applications
    Replies: 0
    Last Post: 08-28-2009, 06:17 AM
  4. Ideas for structuring DB
    By rbpd5015 in forum Alpha Five Version 8
    Replies: 6
    Last Post: 10-24-2007, 09:03 PM
  5. ERD Modeling
    By acant05 in forum Alpha Five Version 5
    Replies: 0
    Last Post: 08-29-2002, 03:51 PM

Bookmarks

Posting Permissions

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