Ensuring data integrity
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
Subject
Written By
Posted
Ensuring data integrity
July 30, 2010 09:45AM
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.