Let's say I have an entity
PizzaStore which has a location as part of its attributes
PizzaStore can have N of other
PizzaStore that are close by.
So seems to be a self referencing relationship 1-N (optional)
If we know before-hand which exact
PizzaStore are close by each other what would be the best way to represent that?
Since we have a self-referencing 1-N relationship I think another table would be needed
In this case we would store e.g.
<2, 102> etc to show that pizza store with id 1 has 22, 23 and 78 near by etc
Now in order to get these rows back in-order I would need to create a PK and query based on that.
I was wondering would an auto-increment guarantee the insertion order?
Or would I need to use a float representing the distance e.g.
<2.04, 1, 23> (where 2.04 is the distance in miles)
I was also thinking is there a better way than this?
We know that if
1 is close to
22 is also close to
Is there a more efficient way to represent this information?
I think it would suffice to store just the row
<1,22> to capture that
1 is close to
22 and that
22 is close to
1. But this way we are losing the order
All the answers are very useful. One thing though that perhaps is not clear.
I already have the order of the entities based on the distance on insert time.
I.e. I have a set of ids of objects already sorted based on distance and I want to insert this set in the database in a way that I can retrieve the rows back in the order I have inserted them. I want to avoid sorting on distance on retrieve since I already had the order at insert time. These ids would be used to join to another table (they are ids) and I want to retrieve the rows in the insert order