Something like this:
Party
id
type: {individual, group}
name
PartyRelationship
fromPartyId
toPartyId
type: {groupMembership}
Event
id
name
scheduledStart
scheduledEnd
actualStart
actualEnd
EventRole
eventId
roleType {organizer, scheduler, participant}
partyId
List
id
eventId
name
ListItem
id
listId
description
createdBy /* should point to a party */
An Event hasMany Roles, which could be organizers, participants, etc.
A participant can be either an individual or group, both of which are subtypes of Party
A Group party has many Individual parties, as described in the PartyRelationship table. An Individual can belong to zero+ groups.
An Event hasMany Lists, and a List hasMany Items. A list belongsTo one Event, and a ListItem belongsTo one List.
Example usage:
insert into party values (1, 'individual', 'neil');
insert into party values (2, 'individual', 'bernk');
insert into party values (3, 'individual', 'simon');
insert into party values (4, 'individual', 'ypercube');
insert into party values (5, 'group', 'canadians');
insert into partyRelationships values (5, 1);
insert into partyRelationships values (5, 2);
/* now neil and bernk are related to the 'canadians' party */
insert into event values (1, 'an event with canadians and ypercube, organized by simon', '2012-07-01', '2012-07-02');
insert into eventRoles values (1, 'organizer', 3); /* simon is the organizer*/
insert into eventRoles values (1, 'participant', 5); /* the 'canadians' group is a participant */
insert into eventRoles values (1, 'participant', 4) /* ypercube is a participant too */
First, since you already have a UNIQUE index that contains the user_id, you should be able to get rid of the id field, and use the UNIQUE index as the PRIMARY KEY:
CREATE TABLE `twitter_relationships` (
`user_id` int(11) NOT NULL,
`source_twitter_id` bigint(20) NOT NULL,
`target_twitter_id` bigint(20) NOT NULL,
`relationship_status` tinyint(1) NOT NULL,
`status_change_date` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`user_id`,`source_twitter_id`,`target_twitter_id`),
KEY `target_status_and_change_date_index`
(`user_id`,`target_twitter_id`,`relationship_status`,`status_change_date`),
KEY `user_id_index` (`user_id`,`status_change_date`)
) ENGINE=InnoDB AUTO_INCREMENT=116597775 DEFAULT CHARSET=latin1
PARTITION BY HASH (user_id) PARTITIONS 1000;
Unfortunately, while this removes an index, it may increase storage requirements, due to the way that InnoDB indexes data. See "How Secondary Indexes Relate to the Clustered Index" in http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html
Second, while the source_and_target index has two of the three fields in your WHERE clause, MySQL will have to do an additional read to find the relationship_status.
Therefore, to improve performance, create an index that includes all three fields in your WHERE clause:
CREATE INDEX user_source_status ON twitter_relationships
(`user_id`,`source_twitter_id`,`relationship_status`);
Then, if MySQL doesn't use this index automatically, you can force using it, with:
SELECT target_twitter_id
FROM `twitter_relationships` FORCE INDEX (user_source_status)
WHERE (`twitter_relationships`.`user_id` = ?
AND `twitter_relationships`.`source_twitter_id` = ?
AND `twitter_relationships`.`relationship_status` = ?)
LIMIT ?, ?
Lastly, you're missing the UNSIGNED attribute on the id, user_id, source_twitter_id, and target_twitter_id fields. I'm guessing these fields will never store negative values, so it would make sense to make them UNSIGNED.
Best Answer
When designing schemas that have a "current state" (in your case: What inventory is currently in use) and a historical log of states, it is generally a good idea to split those two concepts into two different tables.
In your case, you would have:
And you will have
Whenever
status(or any other thing you want to track) changes, you write a new row ininventory_eventsand update the row ininventory_status. You may even decided to do this with a trigger. You could argue that this is a slight break with normalisation. But until someone invents a temporal database with reasonable performance, this is the fastest and easiest way to query the database.From your description, it is not clear to me if an
inventory_itemcan have more than one status at the same time (for example, can a car be BOTH rented out and stolen at the same time?). If eachinventory_itemcan only have one status, you can collapse the tableinventory_itemandinventory_statusinto one without breaking normalisation.A design like this has the advantage that joins are clean and easy to write. You do no need to look for the latest status - as that is always present in the
inventory_statustable.A lot of ERP systems handle the balance sheet in a very similar manner - with a snapshot table (the balance sheet) and a log of transactions that add up to it. With the risk of jumping to conclusion, this may be the analogy that Pieter is cleverly hinting at.