Results 1 to 5 of 5

Thread: database design

  1. #1
    Join Date
    Apr 2007
    Posts
    3

    Unanswered: database design

    Hi, I am looking to redesign a database for my site. Where I am having problems is a relation between "part" and "assembly" type thing, where there are multiple bosses per employee. Right now I have it so that every boss has their own table, which has become so dreadfully cluttered and inefficient its come to the point where redesigning is the best option. I was thinking that a hierarchy tree would be the best, and what I thought would be the best would to have 1 table of all employees, then when I go to display them for each department have something like

    Code:
    $query = "SELECT 'name, desc' FROM 'Parts' WHERE 'assembly' LIKE '$assembly'"
    and in my database have 3 fields.

    name
    desc
    assembly

    where each part can belong to many assemblies. I know this works for the parts that are only associated with 1 assembly, but what about the parts that have more than 1? the query isnt going to match the assembly, because they are seperated by a delimiter, in my case a "comma ( , )"

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in the general parts/assembly model, yes, often a part can be part of multiple assemblies

    but usually an employee does not have multiple bosses

    i'm sure this is also the case in your situation (one table per boss?!! well, i hope you've learned your lesson! )

    so it's a hierarchy, not a many-to-many, right?

    see Categories and Subcategories

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    3
    oops yah, when i was typing it out i thought about doing it in employee to boss as my example but figured it would be more beneficial to just say what i needed... it is parts to assembly, but what im asking is how do i structure the database so that when part1 is in assembly1 and assembly2... how would I write that into the database and how would I extract it?

    and yes, I have learnt my lesson... its been 6months in my current method and its been a royal pain to maintain... and I am sure that my novice database knowledge doesnt help either.

    so if ya could help with my first paragraph i would appreciate it

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you are asking for is out there on the web, somewhere, probably many times over

    writing it into the database is easy, those are just INSERT statements

    extracting it is easy too, those are just SELECT statements

    what you are looking for is the parts explosion data model

    there are only two tables, with two foreign keys in one of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2007
    Posts
    3
    yes i know how to insert and extract information from a database, my problem is with the "parent" assembly record. I have some parts that have multiple assemblies, that must remain exclusive. but it ignores that parts with multiple assemblies, I am guessing because it is thinking that the parent assembly is "assembly1, assembly2" when in fact my intention is to have them seperate and read as "assembly1", "assembly2".

    I have ran multiple searchs on google, and yahoo, neither of which have produced an example such as what I am looking for, which is why I am here in the first place. I am not saying to structure my database for me, I am looking for a reference of where someone has "already invented the wheel" and I just need to modify it to fit my specific needs.

Posting Permissions

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