MySQL Forums :: General :: Ensuring data integrity


Advanced Search

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 Michael Reed 07/30/2010 09:45AM
Re: Ensuring data integrity Peter Brawley 07/30/2010 10:04AM
Re: Ensuring data integrity Michael Reed 07/30/2010 11:59AM
Re: Ensuring data integrity Peter Brawley 07/30/2010 09:32PM
Re: Ensuring data integrity Michael Reed 07/30/2010 10:46PM
Re: Ensuring data integrity Peter Brawley 07/31/2010 09:24AM
Re: Ensuring data integrity Peter Brawley 07/30/2010 06:09PM
Re: Ensuring data integrity Michael Reed 07/30/2010 07:50PM
Re: Ensuring data integrity Rick James 08/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.