hi,
i have problem in design db of my small system
so i will describe it all to get the whole picture

here what i have:

i have customers with many branches &
each branch has many machines &
each machine exists in many branches

Also : each machine has many components
each component exists in many machines

Now i have :
each branch has many machines with it's components , and each component in
this branch machine has it's serial Number (this the most confusion part)

now how i draw this in ERD and physical table as i am confused ,

thx ,waiting for your appreciated reply

On Mar 26, 8:30 am, "moali" <ano... (AT) yahoo (DOT) com> wrote:
CUSTOMER ---<BRANCH>---MACHINE>---COMPONENT

---< = 1 TO MANY

So,

customer(customer_id*,customer_name,customer_addre ss)
branch(branch_id*,customer_id,branch_name,branch_a ddress)
machine(machine_id*,machine_name)
component(component_id*,component_serial_no,compon ent_name)
branch_machine(branch_id*,machine_id*)
machine_component(machine_id*,component_id*)

*=(Part of) PRIMARY KEY

On Mar 26, 8:30 am, "moali" <ano... (AT) yahoo (DOT) com> wrote:
CUSTOMER ---<BRANCH>---MACHINE>---<COMPONENT

---< = 1 TO MANY
So,

customer(customer_id*,customer_name,customer_addre ss)
branch(branch_id*,customer_id,branch_name,branch_a ddress)
machine(machine_id*,machine_name)
component(component_id*,component_serial_no,compon ent_name)
branch_machine(branch_id*,machine_id*)
machine_component(machine_id*,component_id*)

*=(Part of) PRIMARY KEY

thx for reply
but how can i write for each branch with each machine it's component serial?
as component serial depends on certain machine on certain branch


strawberry wrote:

On Mar 26, 1:12 pm, "moali" <u32812@uwe> wrote:
Because it makes it hard for others to follow the thread of the
conversation
Why?
Try not to top post.

basically there's two ways, depending on the logic that's used to
generate the serial.

1. If the serial no. is arbitrary, then you might need to restructure
the db slightly (I think like this, not sure though):

branch_machine(branch_machine_id*,branch_id,machin e_id)
machine_component(machine_component_id*,machine_id ,component_id)
branchmachine_machinecomponent(branch_machine_id*, component_id*,serial_no)

2. If the serial number can be inferred from the branch, machine &
component (or customer, branch, machine & component) then you don't
need to store it at all, e.g.

If the code for the lid of the straw dispenser 2 at McDonald's outlet
no. 4 was MCD_00000004_STRW002_01, this could potentially be inferred
from information stored elsewhere in the database, you'd simply add a
column to each relevant table to store the respective part of the
code. I think that this is a better approach than the one I describe
above.

moali wrote:
The problem may stem from a lack of detail.

You say that each branch has many machines, and each machine exists in many
branches. I suspect that each *type* of machine exists in many branches, but
each machine exists in only one.

So you need to store [machine type] and [machine] in different tables. You can
then link [machine]*...1[machine type] and [machine]*...1[branch].

If you get stuck thinking about entities, just get some sample data and try to
arrange it into tables, comparing this with E-R models.