MySQL Forums
Forum List  »  General

Ensuring data integrity
Posted by: Michael Reed
Date: July 30, 2010 09:45AM

I haven struggling on how to utilize the database schema to ensure that multiple sets of independent data never gets intertwined.

For instance, say I have bank accounts, members that belong to a given bank account (assume member can only belong to a single account), and transactions. I might do the following:

Accounts
-accounts_id (PK)
-some_data

Members
-accounts_id (PK)
-members_id (PK)
-name, etc

Transcations
-transctions_id (PK)
-accounts_id (FK references members.accounts_id)
-members_id (FK references members. members _id)
-amount
-date

Members uses a composite primary key (members_id is unique for a given accounts_id), and Transactions references Members’s primary key.

I like this structure in that it ensures that a record can never be inserted into Transactions for a Account/Member combo that doesn’t exist.

What I do not like is that if I need a many-to-many relationship with Members, I have to add both accounts_id and members_id to the cross table. In addition to using more space, the larger concern is that the JOIN will require equality of both keys which might reduce speed. Also, I do not know if it is possible to use AUTO_INCREMENT on Members.members_id (I am using MySQL).

Any advice on how best to proceed. Thank you

Options: ReplyQuote


Subject
Written By
Posted
Ensuring data integrity
July 30, 2010 09:45AM
August 01, 2010 01:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.