I am struggling to get an understanding of what is best in my specific scenario to tie together certain 'jobs'. Ive tried different queries and keep getting odd issues.
So looking back to basics and thinking that there may be a better way, need your collective advice on which way to go with this.
Simply put - my database has 3 core main tables and many others for lookup etc. I need to find a method by which to link these tables together so that when queried later - I can see all related info together.
What is common with each is something called a TNE Number. The issue with this number however is that it is not unique and can be duplicated.
t_services, t_hardware_orders and t_los are my 3 main tables, each will then have a field that has a TNE number. When this number matches, it refers to the same 'service'.
Is there a method to link these three tables together or should I consider a new table - lets call it t_job_link (Junction Table) and then have:
ID (PK)
Services_TNE_1 (FK)
Services_TNE_2 (FK)
Hardware_TNE_1 (FK)
Hardware_TNE_2(FK)
Hardware_TNE_3 (FK)
Hardware_TNE_4 (FK)
LOS_TNE_1 (FK)
The link is then between the PK of each of the main tables into these FKs. There is however a lot of manual work that will need to be done to manually update these.
The reason for the multiple entries is that a specific job can have 2 sets of services bought and 4 items of hardware purchased.
Thanks in advance.
So looking back to basics and thinking that there may be a better way, need your collective advice on which way to go with this.
Simply put - my database has 3 core main tables and many others for lookup etc. I need to find a method by which to link these tables together so that when queried later - I can see all related info together.
What is common with each is something called a TNE Number. The issue with this number however is that it is not unique and can be duplicated.
t_services, t_hardware_orders and t_los are my 3 main tables, each will then have a field that has a TNE number. When this number matches, it refers to the same 'service'.
Is there a method to link these three tables together or should I consider a new table - lets call it t_job_link (Junction Table) and then have:
ID (PK)
Services_TNE_1 (FK)
Services_TNE_2 (FK)
Hardware_TNE_1 (FK)
Hardware_TNE_2(FK)
Hardware_TNE_3 (FK)
Hardware_TNE_4 (FK)
LOS_TNE_1 (FK)
The link is then between the PK of each of the main tables into these FKs. There is however a lot of manual work that will need to be done to manually update these.
The reason for the multiple entries is that a specific job can have 2 sets of services bought and 4 items of hardware purchased.
Thanks in advance.