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.
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.
