Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 Files

  • #2
    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
    .

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

    Comment


    • #3
      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?

      Comment


      • #4
        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
        .

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

        Comment


        • #5
          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

          Comment


          • #6
            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
            .

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

            Comment


            • #7
              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.

              Comment


              • #8
                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

                Comment


                • #9
                  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
                  .

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

                  Comment


                  • #10
                    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, 10:46 AM.

                    Comment

                    Working...
                    X