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.

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

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/

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

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