On Nov 19, 2010, at 6:37 AM, Mene wrote:
> Hi,
>
> I want an voting application, for a huge building. Each user has one
> room at each floor of the building. The room is shared with other
> users and may change over time. Each user has one vote for each room
> he is in (so also one per level) to vote for things like the color the
> room shall be painted in, his favorite canvas, preferred music etc.
> The number of rooms and the number of users is expected to be giant,
> but the number of votes is probably relatively low, also in the future
> there will be other voting options.
This schema doesn't illustrate the association of a user/photo combination with
a particular room. I see table names "tile_user" and "photo" which are not
illustrated here. So I can't answer how user.photo_votes[room_name] would be
accessed. Also there's some terminology breakdown here, are you using the
term "photo" to describe things like "the color the room shall be painted in",
"his favorite canvas" ?
I don't see the need for room_user.room_user_id as far as allowing particular
mutations - natural primary keys don't prohibit anything as long as you have ON
UPDATE CASCADE available. If the reason is that you just want surrogate
primary keys, then you should have one for "room" as well otherwise it's
inconsistent.
Its fine to have tables which associate users with all their possible rooms,
and all possible photos with a room. Storing "user x voted for photo y in room
z" would need a "fact" table, however, along the lines of:
user_votes = Table('user_votes', metadata,
Column('user_id', Integer, ForeignKey('user.id', ondelete='CASCADE'),
primary_key=True),
Column('photo_id', Integer, ForeignKey('photo.id',ondelete='CASCADE'),
primary_key=True)
Column('room_name', Unicode(8), ForeignKey('room.room_name',
onupdate="CASCADE"), primary_key=True)
)
>
>
> I want the following database layout
>
> // Simple user table
> user_table = Table('user', metadata,
> Column('user_id', Integer, primary_key=True)
> )
>
> // Simple room table
> room_table = Table('room', metadata,
> Column('room_name', Unicode(8), primary_key=True)
> )
>
> // Table associating a user with a room.
> // The surrogate key is needed, because users may move to other rooms
> and I want the database to be
> // referential integer
> room_user_table = Table('room_user', metadata,
> Column('room_user_id', Integer, primary_key=True),
> Column('room_name', Unicode(8), ForeignKey('room.room_name')),
> Column('user_id', Integer, ForeignKey('user.user_id',
> ondelete='CASCADE'))
> )
>
> // Defines which photos are available for this room
> room_photo_table = Table('room_photo', metadata,
> Column('room_name', Unicode(8), ForeignKey('room.room_name'),
> primary_key=True),
> Column('photo', Integer, ForeignKey('photo.id',
> ondelete='CASCADE'), primary_key=True)
> )
>
> // Stores the votes of each user
> photo_vote_table = Table('photo_vote', metadata,
> Column('tile_user', Integer, ForeignKey('tile_user.id',
> ondelete='CASCADE'), primary_key=True),
> Column('photo_id', Integer, ForeignKey('photo.id',
> ondelete='CASCADE'))
> )
>
>
> I need to access the votes from python like this:
> user.photo_votes[room_name] = photo
> This should work for read and write access.
>
> I'd get it working if I'd drop the photo_vote_table and store the
> photo_id in the room_user_table, however, this would lead to a very
> sparse database and I'll be in need to add more and more fields to
> room_user_table, as more voting options become available.
>
> The mapping for this looked like:
>
> user_mapper = mapper(User, user_table,
> '_photos_votes_dict':relation(RoomUser, lazy=True, cascade="all,
> delete" \
> ,
> collection_class=column_mapped_collection(room_user_table.c.room_name)
> )
> })
>
> class User(object):
> photos_votes = association_proxy('_photos_votes_dict', 'photo')
>
> (the rest is trivial)
>
> I hope I mentioned everything and you understood me ;D
>
> Greetings and thanks for your time,
> Mene
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
