I'm designing a db for a project which has tables:
mainAccount,
id int(11) auto_inc primary key
username,
password,
country,
email,
verification
users,
id int(11) auto_inc primary key
`type` tinyint (3) -fk userType.id
description nvarchar
interests nvarchar
website nvarchar
(many other columns..)
userIndex,
user int(11) primary key - fk users.id
`type` tinyint (3) -fk userType.id
mainAccount (11) - fk mainAccount.id
displayName nvarchar
displayImage int(11) - fk images.id
userType,
id tinyint(3) auto_inc primary key
allowImages bool
allowDiscussions bool
allowSettings bool
allowContacts bool
discussions,
id int(11) auto_inc primary key
engine=myissam
images,
id int(11) auto_inc primary key
user int(11) - fk users.id
mainAccount (11) - fk mainAccount.id
filename nvarchar
settings,
user int(11) - fk users.id
`type` tinyint (3) -fk userType.id
displayImages bool
displayDiscussions bool
displaySettings bool
displayContacts bool
primary key(user,type)
contactLists.
user1 int(11) - fk users.id
user2 int(11) - fk users.id
user1type tinyint (3) -fk userType.id
user2type tinyint (3) -fk userType.id
primary key(user1,user2)
This is a basic design is:
Users create one mainAccount (id int(11) auto_inc, has username/password/verification columns)
Their mainAccount can create multiple user accounts.
The userIndex is created to make lookups on users much faster. Most usage will only require the name/image from the index table so I decided to vertical partition it out of the old user table.
I started my design using innodb so foreign keys could help things and make updates/deletes/db consistency easier. Because innodb lacks fulltext search support I converted discussions to myissam. Now it seems that foreign keys will have to be dropped period because as the db grows it'll need to be to partitioned or maybe switched to a federated storage engine - which means I'll lose foreign key support. Right now I can get away with fkeys because the db is small but since I need to design for scalability it looks like I'll have to drop all the foreignkeys at the start and design around it.
The userType is denormalized to death. It's id is stored in other tables often to make index lookups quick for those tables (no need to do extra joins). It seems I'm stuck trying to keep all these indexes in sync without using foreign keys. I was considering making mainenance scripts run to re-link any missing or mislinked user/usertype entries by pulling the solid copy from the main userIndex table.
Does anyone have any experience coding to keep thinks linked properly without foreign keys? I'm using some transactions for all or nothing when things need to be linked for sure but watching for and trying to keep db consistency in the code is making me worry. I'm trying to think how large sites cope with this to keep things from falling out of sync,
Thanks for y our time
Jeck Lamnent