Is This Normalization Correct? (Two Many-To-Manys Connected by a Many-To-One)

Should I use a many-to-many relationship or obtain info through one-to-many relationships?

When a many-to-many relationship exists, say gameUsers, a common implementation is to create an intermediary table, containing the ids of each of the tables, in this case a list of user_id, game_id pairs representing which games each user is in.

But... you already have such a table, the player table. So my approach would be to think of the player table as that resolution table. The player table already contains which games each user is in, so that second many-to-many is not really required.

Is a separate database table required if it's in a many-to-many relationship and only has a single column?

I would recommend sticking to the original design with three separate tables, and an integer primary key. One reason is that you want keys that are meaningless, ie that have no business signification.

With the alternative design that you are thinking about, what if you want to rename a genre, say from Sci-Fi to Science Fiction? You would need to update every row in the bookgenre table, which is unecessarily complex.

Primary keys should never be updated, so if there is a risk that you might need to ever change the value of a genre, you should go with surrogate keys: this will make your life easier on the long run.

Many-to-many relationship as list

You are right in the m2m field.
But in scores it would be a new model

Class score:

    Player = ForeignKey

    Game = ForeignKey

    Score = Integer

How to map different many to manys with same constraint

Usually, you run into this kind of problem when you've split a key. Fixing that split key, then using overlapping foreign key constraints is usually what you're looking for.

create table cmember (
  cmemberid integer primary key,
  username varchar(15) not null,
  emailaddress varchar(64) not null
);

create table application (
  applicationid integer primary key,
  description varchar(50) not null
);

create table member_application (
  cmemberid integer not null references cmember (cmemberid),
  applicationid integer not null references application (applicationid),
  primary key (cmemberid, applicationid)
);

create table role (
  roleid integer primary key,
  rolename varchar(25) not null
);

create table crole (
  croleid integer not null references role (roleid),
  -- Include the application id in this table . . .
  applicationid integer not null references application (applicationid),
  -- and make it part of the primary key.
  primary key (croleid, applicationid)
);

create table member_role (
  cmemberid integer not null references cmember (cmemberid),
  croleid integer not null,
  applicationid integer not null,
  primary key (cmemberid, croleid, applicationid),
  -- Note the overlapping foreign key constraints.
  foreign key (croleid, applicationid) references crole (croleid, applicationid),
  foreign key (cmemberid, applicationid) references member_application (cmemberid, applicationid)
);

insert into cmember values (1, 'A', 'A@b.com');
insert into cmember values (2, 'B', 'B@b.com');

insert into application values (1, 'App 1');
insert into application values (2, 'App 2');

insert into member_application values (1, 1);
insert into member_application values (2, 2);

insert into role values (1, 'Admin');

insert into crole values (1, 1);
insert into crole values (1, 2);

insert into member_role values (1, 1, 1);
insert into member_role values (2, 1, 2);

Member 1 is assigned only to application 1. So trying to insert a row that references application 2 should fail.

insert into member_role values (1,1,2);
ERROR:  insert or update on table "member_role" violates foreign key constraint "member_role_cmemberid_fkey1"
DETAIL:  Key (cmemberid, applicationid)=(1, 2) is not present in table "member_application".

sql alchemy column value dependent on other table

You have several ways to implement you idea:

1. Easiest way: you can update your columns with update query, something like this:

       try:
           player = Player(name='New_player_name', team_id=3)
           Session.add(player)
           Session.flush()
           Session.query(Team).filter(Team.id == Player.team_id).update({Team.players_number: Team.players_number + 1})
           Session.commit()
       except SQLAlchemyError:
           Session.rollback()
           # error processing
   

2. You can implement sql-trigger. But an implementation is different for different DBMS. So, you can read about it in the documentation of your DBMS.

3. You can implement SQLAlchemy trigger, like this:

    from sqlalchemy import event

    class Team(Base):
        ...

    class Player(Base):
       ...

        @staticmethod
        def increment_players_number(mapper, connection, player):
            try:
                Session.query(Team).filter(Team.id == player.team_id)\
                         .update({Team.players_number: Team.players_number + 1})
            except SQLAlchemyError:
                Session.rollback()
                # error processing

    event.listen(Player, 'after_insert', Player.increment_players_number)

4. As you see, there are always two queries, because you should perform two procedures: insert and update. I think (but I'm not sure) that some DBMS can process queries like this:

    UPDATE table1 SET column = column + 1 WHERE id = SOMEID AND (INSERT INTO table2 values (VALUES))

Are tables with the same attributes but different relationships bad practice?

It is ok to have tables with the same attributes. Just find sufficient meanings/predicates to record your application state.

A base table has a "meaning" or "predicate (expression)" that is a fill-in-the-(named-)blanks statement given by the DBA. The names of the blanks of the statement are the columns of the table. Rows that fill in the blanks to make a true proposition about the world go in the table. Rows that fill in the blanks to make a false proposition about the world are left out. Ie a table holds the rows that satisfy its statement. You cannot set a base table to a certain value without knowing its statement, observing the world and putting the appropriate rows into the table. You cannot know about the world from base tables except by knowing its statement and taking present-row propositions to be true and absent-row propositions to be false. Ie you need its statement to use the database.

And just declare the relationships/FKs as appropriate. The R in ER is for relationship meaning association (among entities). However many self-styled "ER" methods and products use "relationship" for foreign key (FK) instead. A FK from some roles/columns in one association set/table to some in another just means that every entity/subrow for those roles/columns in the source must appear in the corresponding roles/columns of the target as a key. When that is so, declare the relationship/FK.

A constraint expression just corresponds to a proposition aka always-true statement about the world and simultaneusly to one about base tables.

(From this answer. See this answer re attributes/headings, this one re predicates and this one re relationships/FKs/links.)

---

Related Topics