0

I'm trying to design the relationship between these two entities in my model:
A Lick is an ordered sequence of Notes (that in return can be part of different licks), so I decided to implement a many-to-many with a position field in the association class. I would like to add an unique constraint to my Lick class, to avoid duplicates, but I don't know how to do it in a ORM way. The only solution that I've come up with is to set a custom id field in the Lick class (instead of the usual incremental integer) that I'm sure being unique, but I feel that I'm basically re-encoding in a string what is already present in the database. Is there a way to set a unique field in the Lick class that relates to the ordering of its notes?

Thanks in advance. Below you can see my model:

class LickNote(db.Model):
    __tablename__ = 'lick_note'
    __table_args__ = (
        db.ForeignKeyConstraint(('note_string', 'note_fret', 'note_value'),
                                ('note.string', 'note.fret', 'note.value')),
    )
    lick_id = db.Column(db.String, db.ForeignKey('lick.id'), primary_key=True)
    note_string = db.Column(db.Integer, primary_key=True)
    note_fret = db.Column(db.Integer, primary_key=True)
    note_value = db.Column(db.Integer, primary_key=True)
    position = db.Column(db.Integer)
    notes = db.relationship("Note")


class Lick(db.Model):
    """
    A lick is identified as a sequence of notes. The identifier is a string in this form:
    ssffvvssffvv.....ssffvv, where ss refers to the string (00-12), ff to the fret (00-23) and vv to the value of the
    note (00-11). This sequence is guaranteed to be unique 
    """
    __tablename__ = 'lick'

    id = db.Column(db.String, primary_key=True)
    notes = db.relationship("LickNote")



class Note(db.Model):
    """
    Value represents the int conversion of the note [0,11]. Marked as primary to identify the possibility of having
    different tunings for the same position on the fretboard
    """
    __tablename__ = 'note'

    string = db.Column(db.Integer, primary_key=True)
    fret = db.Column(db.Integer, primary_key=True)
    value = db.Column(db.Integer, primary_key=True)
EsotericVoid
  • 2,306
  • 2
  • 16
  • 25
  • Hi. "add an unique constraint to my Lick class, to avoid duplicates" is unclear. Give exact unique & FK constraints--although the constraint you actually want seems to be something else merely *reminiscent* of those. PS Google problems with anti-pattern ["smart keys"](https://stackoverflow.com/a/34082143/3404097). In relational designs the smallest parts you want to query about should have their own columns. This is the principle behind some "1NF"s. (Although ["1NF" has no single meaning](https://stackoverflow.com/a/40640962/3404097) & is not the same sort of thing as higher NFs.) – philipxy Oct 18 '18 at 23:45
  • @philipxy thanks for your answer. So basically a lick is defined as a series of notes. If two licks have the same notes in the same order, they are equal. Do you have any suggestion for the implementation? – EsotericVoid Oct 19 '18 at 06:11
  • Read & follow an introductory academic textbook on information modeling & relational DB design. Relationally, use a surrogate id for Licks. For demonstrated needed performance improvement you might want a redundant column that is a certain string representation of its consitutent notes. PS Tables represent relationships. Some relationships are functional & can be implemented as functions. Any design is a tradeoff between what we put in tables & what we put in functions. Tables allow generic querying using predicate logic ("...where there exists...") with a certain computational cost. – philipxy Oct 19 '18 at 06:52
  • PS [Re a redundant column that is a function of the database state.](https://stackoverflow.com/a/45395193/3404097) PS An invoice "is" some stuff & a sequence of order lines but we don't represent it relationally as (a string encoding of) a record of which one field is a list of records. – philipxy Oct 19 '18 at 06:59

0 Answers0