SQL Tables and Releation (1 Viewer)

Hi.

I have a question which might seems little odd, but I hope I could get some guidance here...

I have a main table, lets call it Project.
I have some seondary tables, which have the realtion back to the Project table, with the Project ID as relation link.
Then - under one of the 'secondary' tables - lets call it Product - i have a table called Location. This Location have releated info into the parrent table Product. So, we could say that Location's parent are Product, and Product's Parent are Project.

The question is - from your experiences - schould I use the same relation (Project ID) for all three tables? The reason I ask, is becaus in the above scenarion, I would have the same Product ID and Location ID in several Projetcs, so it's impossible to use a unique Product and Location ID. Of course, I could use Autonumber in Product table and relate down to Location, but I'm not sure if this is neccessary - if I use the same key for all sub table.
For me, it's not a problem to use sql queries as source for forms both ways, but I'm just not sure what is the most useful solution. And I'm afraid f I start of wrong, I will regrett later.
I'm familiar with two 'levels' (parant-child) of tables, but now I have to deal with three 'levels' of tables (parent - child - childs child), and I see the challange...

Best regards,
Geirr

The question is - from your experiences - schould I use the same relation (Project ID) for all three tables?

Click to expand...

No. Your relationships should resemble tree branches (one breaking off from only one other) and not spider webs (all tables connected to one another). So, Location should have ProductID in it, not ProjectID--assuming it really is related to a Product.

I would have the same Product ID and Location ID in several Projetcs

Click to expand...

That contradicts the initial relationship you described. You initially described the relationship between Projects and Products as 1 Project to Many Products. The above sentence says the relationship is many to many. Which is it?

At this point I think you should drop the generalities and tell us the specifics of your data--tell us the actual tables and their purposes.

Hi.

Thanks for your reply!
I did'nt mean to be to general, but the problems is that I will deal with several projects. In each project I will have the same products (product ID) and location (location ID), but the combination of product and location will not be the same for each project. So, no - I don't see the many to many, but I understand your comment.

I'm in a early stage on this case, and I don't have a clear solution I could present for you - thats why my question where based on little concrete table info - and I was hoping for some 'consideration feedback', which I believe you gave me.

Please excuse my lousy english writing..

BR. Geirr.

Lousy English? I would have never guessed it wasn't your native language until you said that.

Language is fine, its the logic I am getting lost on. You really need to determine the relationship among your tables. Check this link out for the relationship types:

https://support.office.com/en-us/ar...ionships-30446197-4fbe-457b-b992-2f6fb812b58f

It may be you need a junction table to sort out the relationships among some of those entities.

I think part of the issue may be in stating how things are actually related.

Is the location related to the project?
Does a "Product" have only one location?

Often when you write out exactly how you are relating items you come to realize you need more tables than you originally expected.