0

To describe an NFS mount in a Database I have the following tables (simplified):

NFS_Export: id (PK), name, path
NICs: id (PK), name
NFS_Export_NIC_priorities: id (PK), nfs_export_id (FK), nic_id (FK)

My problem is that the NICs used for an NFS export are a list where the order gives the priority and the user must be able to change that order.

Currently I order the NICs by the NFS_Export_NIC_priorities id. The lower the id the higher the priority. But that means to resort the order I have to swap two or more entries which sounds like a bad idea.

Is there a better way to store a list where the order matters and is changable?

Goswin von Brederlow
  • 11,875
  • 2
  • 24
  • 42
  • 1
    Why don't you simply add a column myorder(int) to the NICs table where you store the ordinal number ? – kiks73 May 02 '19 at 13:14
  • How does that help? Then I still have to change two or more entries updating the ordinal number every time the order is changed. – Goswin von Brederlow May 02 '19 at 13:26
  • This has been discussed before: https://stackoverflow.com/questions/2826829/whats-the-best-way-to-store-sort-order-in-sql – Salman A May 02 '19 at 13:39
  • While the question seem to be the same there is no satisfactory answer there. Is there really no solution for this that doesn't (periodically) require rewriting the whole table? – Goswin von Brederlow May 02 '19 at 13:47
  • @GoswinvonBrederlow I probably do not understand your question, but why not use ORDER BY statement to reorder by myorder new column? – kiks73 May 02 '19 at 14:52
  • Sorting by myorder is not the problem. The problem is later changing that order. If I have "nic1, nic2, nic3" and then I want "nic2, nic3, nic1" then I have to change the myorder column on all 3 entries. – Goswin von Brederlow May 02 '19 at 15:20

0 Answers0