Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: Suggestion for DB Structure/Model

    Hi Guys/Girls,

    I'm in the design phase of a new project and I am looking for a suggestion on how to manage and develop my database. The idea is very simple.

    There will be a (P)Parent Category and a (C)Child Category:

    Code:
    - (P) Camera
      - (C) Model ABC
      - (C) Model DRE
      - (C) Model OUH
    - (P) Lens
      - (C) Model AHE
      - (C) Model XJE
    - (P) Flash
      - (C) Model KUE
      - (C) Model SKE
    Finally there will be a table of problem/solution content (posts). Each one of these can belong to one or many of the different Child Categories.

    My question is, how would you recommend I flesh out my database and relationships?

    My idea was the following:

    Create two tables. A parent table consisting of ID and Parent Name. A Child table consisting of ID | Child Name | Parent Relationship.

    Code:
    ID  |  Parent           ID  | Child      | Parent Relation
    --------------        ---------------------------------------
    101 |  Camera          1001 |  Model ABC | 101
    102 |  Lense           1002 |  Model DRE | 101
    103 |  Flash           1003 |  Model OUH | 101
    104 |  Video           1004 |  Model AHE | 102
                           1005 |  Model XJE | 102
                           1006 |  Model KUE | 103
                           1007 |  Model SKE | 103
    Finally create a master table with all the posts and a relationship to a child.

    Code:
    ID    |  Post Content          |  Child Relationship
    -------------------------------------------------------------------
    10001 | Issue this and that    | 1002
    10002 | Issue that and this    | 1004, 1006, 1001
    10003 | Issue here and now     | 1005, 1002
    10004 | Issue where and how    | 1001, 1002, 1003, 1004, 1005
    With this method I could build a search funtion that searches the post content and shows the post and what products (children) it relates to. I could also have an advanced search that searches within a child only in which case the search would parse the master table contents that match a certain child relationship.

    If a post is deleted there is no change to the parent and child tables. If a child is deleted then only the relationship in a post content is edited. If a parent is deleted all corresponding children are deleted and child relationships.

    Any help will be greatly appreciated!

  2. #2
    Join Date
    Feb 2012
    Location
    Peru
    Posts
    1
    I suggest a hierarchical structure based on two fields in the same table:

    Code:
    id   |  Data    |  parent_id
    ------------------------------
    1    |  ...     |  0                    -- it means is a root item
    2    |  ...     |  1 
    3    |  ...     |  1 
    4    |  ...     |  0 
    5    |  ...     |  4 
    6    |  ...     |  4
    I would be more easy to extract data.

  3. #3
    Join Date
    Feb 2012
    Posts
    2
    Sorry, i'm not sure I follow. Isn't that basically the solution I posted minus multiple child/parent relationships? I need to be able to list all the problem/solutions for a particular model. Vice-versa I need to be able to view a Problem/Solution and see all the models it applies to. I want to be able to do this without duplicating data.
    Last edited by Vidarious; 02-28-12 at 01:17.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not to certain if this is answering the question or answering the hypothetical example

    I'd have a table for
    product types :- eg cameras, flash, lenses, etcd...
    products: describes each product
    ProductGozinto: associates different product with each other eg Flash ABC fits Camera 123, Camera 234. the primary key being the two product ID's
    Posts table
    PostLinks which associates a specific post with specific product(s)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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