Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    The Netherlands
    Posts
    3

    database design problem

    Hello,
    i'm designing a database, but i have the following problem

    i have the following tables

    pc(personel computer)
    -serialcode(PK)
    -processor
    -memory
    -harddrive
    -roomcode(FK)

    equipment
    -serialcode(PK)
    -kind_of_machine (if it as printer,scanner..........)
    -pursche_date
    -roomcode(FK)

    room
    -roomcode(PK)
    -roomnumber
    -location

    problem
    -problemid(PK)
    -explantion
    -solution
    -report_date
    -categorie
    -serialnumber(FK)

    the relation ships between pc and problem are many to many
    and the relation to equipemt and problem are many to many
    serialcode in PC and Equipment is one and the same thing like
    PC1 = 0001
    PC2 = 0002
    Printer

    The problem is a need to make a link between pc and quipment and problem
    normally if you had only pc and problem i would take a field from pc and a field from problem make it foreing keys, make a new table which these two field are one primary key(third normalisation form)

    But now i have two tables instead of one and i don't now how to resolve this.
    Does anyone have any ideas???

    The reason i have the two tables PC/Equipment is that if i would put it in one table that if you have quipent that is not a pc you don't fill in the fields : processor,memory and harddrive and i think this wasting space.

  2. #2
    Join Date
    May 2003
    Location
    The Netherlands
    Posts
    3

    database design problem

    Something went wrong with the first post so here it is again. :-)

    Hello,
    i'm designing a database, but i have the following problem

    i have the following tables

    pc(personel computer)
    -serialcode(PK)
    -processor
    -memory
    -harddrive
    -networkadapter
    -roomcode(FK)

    equipment
    -serialcode(PK)
    -kind_of_machine (if it as printer,scanner..........)
    -pursche_date
    -roomcode(FK)

    room
    -roomcode(PK)
    -roomnumber
    -location

    problem
    -problemid(PK)
    -explantion
    -solution
    -report_date
    -categorie
    -serialnumber(FK)

    the relation ships between pc and problem are many to many
    and the relation to equipemt and problem are many to many
    serialcode in PC and Equipment is one and the same thing like
    PC1 = 0001
    PC2 = 0002
    Printer1 = 0003
    PC3 = 0004

    The problem is a need to make a link between pc and quipment and problem
    normally if you had only pc and problem i would take a field from pc and a field from problem make it foreing keys, make a new table which these two field are one primary key(third normalisation form)

    But now i have two tables instead of one and i don't now how to resolve this.
    Does anyone have any ideas???

    The reason i have the two tables PC/Equipment is that if i would put it in one table that if you have equipent that is not a pc you don't fill in the fields : processor,memory,networkadapter and harddrive and i think this wasting space.

    Now should i go for wasting this space or does anyone has other ideas.

    Thanks in advance,
    M.Zoet

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the relationship isn't many-to-many, because each problem can be related to only one serialnumber

    as you suggested, it requires a third "relationship" table

    furthermore, with the current structure, if you wanted to list some problems together with the PC or equipment they dealt with, you would have to conditionally join the problem table to two tables

    finally, the serialnumber in the problem table cannot be declared as a database FK (even though you might use it that way -- for example, FKs are used in mysql all the time, even though mysql doesn't support FKs)

    your reason stated for "wasting space" is not that big a deal, since you would declare those fileds null and thus they would take up no space (just the null bit)

    rudy
    http://r937.com/

  4. #4
    Join Date
    May 2003
    Location
    The Netherlands
    Posts
    3
    Thanks for the answer

    What you are saying is that i should put everything in one table.
    and the fields that we aren't using(sometime) doens't take any space, so they should be of any problem.
    Because i looked at it, and i can't think of a other structure for this database.

    M.Zoet

  5. #5
    Join Date
    Apr 2003
    Posts
    191
    Hi,

    >your reason stated for "wasting space" is not that big a deal, since you >would declare those fileds null and thus they would take up no space >just the null bit)

    it depends ...

    DB2 stores NULL information in an additional byte. Microsoft SQL Server uses bit patterns to distinguish NULLs (I don't know exactly how this works).

    So if space is a concern, only VARCHAR datatype may be your friend.

    Johann

    Originally posted by r937
    the relationship isn't many-to-many, because each problem can be related to only one serialnumber

    as you suggested, it requires a third "relationship" table

    furthermore, with the current structure, if you wanted to list some problems together with the PC or equipment they dealt with, you would have to conditionally join the problem table to two tables

    finally, the serialnumber in the problem table cannot be declared as a database FK (even though you might use it that way -- for example, FKs are used in mysql all the time, even though mysql doesn't support FKs)

    your reason stated for "wasting space" is not that big a deal, since you would declare those fileds null and thus they would take up no space (just the null bit)

    rudy
    http://r937.com/

Posting Permissions

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