0

I want to design a PostgreSQL database for my product which needs to handle ordered many to many relation. There is two solution for that:

  • (Normalized DB)create a middle table and put the order of relations in that
  • (Denormalized DB)use denormalized database and save all data in one table

My data model is like this:

Table 1(Exercise):

  • id

  • name

Table 2(Workout):

  • set of Exercises with order

every user can create custom workout(list of exercises with defined order). my problem is saving the order of relations in database, because default relation not preserve order.

kAvEh
  • 534
  • 4
  • 15
  • https://stackoverflow.com/a/32525594/1835769 – displayName May 21 '19 at 13:29
  • @displayName thanks for reply. In general I know the cons and pros of both but I want to know best-practice for handling ordered relation. – kAvEh May 21 '19 at 13:34
  • 1
    Normalization *is* the best practice. However, after 3NF, in general, it starts hurting the performance more than it adds value. – displayName May 21 '19 at 13:37
  • Also, [here](https://stackoverflow.com/a/32517139/1835769) is the answer to a similar question asked a long time ago. My answer is downvoted, but I think it will be useful. – displayName May 21 '19 at 13:42
  • 2
    I don't understand the question. What do you mean by "ordered" or "Exercises with order"? Normalisation of a schema gives you tables with key(s). It's a common misconception to think keys are ordered. If you want to say some row is 'greater than' some other row, you need column(s) whose values represent that. But the table is not thereby ordered. You might write a query with `ORDER BY` that presents rows in some physical sequence. That doesn't make the table ordered. – AntC May 21 '19 at 23:53
  • This is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When describing a result: Say enough that someone could go away & come back with a solution. You don't. You don't even give an example so we can't even guess what that is. What are the exact designs you are proposing. What is the exact specification it must meet? Nb "more efficient" doesn't mean anything. Define it. But see my next comment. – philipxy May 22 '19 at 06:40
  • 2
    My current generic comment re "better"/"best" etc: There's no such thing as "better"/"best" in engineering unless you define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". https://meta.stackexchange.com/q/204461 – philipxy May 22 '19 at 06:41
  • @AntC "ordered" means custom order. every set of exercises must have custom order that is different from each other. `ORDER BY` will sort the result with pre-defined order but in this case every user can create customized workout with any combination of exercises. my problem is saving order of user custom list in db. – kAvEh May 22 '19 at 06:45
  • @philipxy thanks for advise. you are right. I will edit the post for more clarification. – kAvEh May 22 '19 at 06:49
  • I am saying helpful correct things & the suggestion that I am humiliating anyone or not understanding the problem or wasting anyone's time are unfounded. [ask] [mcve] – philipxy May 22 '19 at 09:56
  • Your question is not clear, but since you say "my problem is saving the order of relations [rows??] in database", maybe you are trying to ask something like this: [Best representation of an ordered list in a database?](https://stackoverflow.com/q/9536262/3404097) – philipxy May 22 '19 at 10:13
  • @philipxy I searched it for two days and read about 30 post in stackoverflow and about 20 article in other sites but not founding any convincing solution. you told about being more clear in previous comment and I edit my post several times. if my question still needs clearance you can ask for it. I know you want to help me but as I said I am not rookie. – kAvEh May 22 '19 at 10:17
  • I'd like to help you, but unfortunately your writing is not clear. I have no idea what you want a solution to. "Use enough words, sentences & references to parts of examples to clearly & fully say what you mean." (Also it seems like maybe you are sometimes saying "relation" when you mean "row".) Good luck. – philipxy May 22 '19 at 10:25

1 Answers1

0

As has been said in the comments, "best practice" supposes that there is exactly one best way to do things, and that is not the case. In pretty much all software design solutions, you have to trade things, in messy, unpredictable ways, which are nearly always context-dependent.

If I understand your question, your problem domain has the concept of a "user" who creates zero or more work-outs, and each work-out has one or more exercises, and the sequence in which that exercise occurs is an important attribute of the relationship between workout and exercise.

The normal way to store attributes of a many-to-many relationship is as additional columns on the joining table.

The normalized way of storing this would be something like:

user
-----
user_id
name
...

Workout
-----
workout_id
name
....

Exercise
------
exercise_id
name
....

workout_exercise
----------
workout_id
exercise_id
sequence -- this is how you capture the sequence of exercises within a workout
... -- there may be other attributes, e.g. number of repetitions, minimum duration, recommended rest period

user_workout
--------
user_id
workout_id
.... -- there may be other attributes, e.g. "active", "start date", "rating"

In terms of trade-offs, I'd expect this to scale to hundreds of millions of rows on commodity hardware without significant challenges. The queries can get moderately complex - you'll be joining 4 tables - but that's what databases are designed for. The data model describes (what I understand to be) the problem domain using separate entities etc.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52