one to optional many Relationship

I want to create one to optional many relation between following two table.

'FK_transactionmember' FOREGIN KEY ('MemberID') REFERENCES 'member' ('ID')
 

I create foreign key constrain witch is one-to-many relation(Above). In this case member ID must required for transaction, but member is optional.


How to control this optional situation?


Memebr:

CREATE TABLE `member` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `FirstName` varchar(30) DEFAULT NULL,
 `LastName` varchar(30) DEFAULT NULL,
 `PermanentAddress` varchar(100) DEFAULT NULL,
 `TemporaryAddress` varchar(100) DEFAULT NULL,
 `Zip` varchar(30) DEFAULT NULL,
 `City` varchar(30) DEFAULT NULL,
 `LastVisit` datetime DEFAULT NULL,
 `TotalVisit` datetime DEFAULT NULL,
 `Active` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`ID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 

Transaction

CREATE TABLE `transaction` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `MemberID` int(11) DEFAULT '0',
 `UserID` int(11) DEFAULT NULL,
 `Total` float DEFAULT NULL,
 PRIMARY KEY (`ID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 

---

Answer 1 :

There are (at last) two ways create table like below:

CREATE TABLE `transaction` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`MemberID` int(11) NULL,
`UserID` int(11) DEFAULT NULL,
`Total` float DEFAULT NULL,
PRIMARY KEY (`ID`),
CONSTRAINT 'FK_transactionmember' 
    FOREGIN KEY ('MemberID') 
    REFERENCES 'member' ('ID')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

and set MemberID to null whenever you can't connect it with member. It's fine in my opinion but it doesn't meet BNF requirements or use different table to link both tables like below:

CREATE TABLE `MemberTransactions` (
`MemberID` int(11) NULL,
`TransactionID` int(11) DEFAULT NULL,
CONSTRAINT 'FK_member' 
    FOREGIN KEY ('MemberID') 
    REFERENCES 'member' ('ID')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
CONSTRAINT 'FK_transaction' 
    FOREGIN KEY ('TransactionID') 
    REFERENCES 'transaction' ('ID')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

and then transaction table without MemberID column.



*************


Answer 2 :



If the MemberID is optional then its default value must be null, not zero. Therefore if no data exists the null value will be allowed.

CREATE TABLE `transaction` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `MemberID` int(11) DEFAULT NULL,
    `UserID` int(11) DEFAULT NULL,
    `Total` float DEFAULT NULL,
    PRIMARY KEY (`ID`),
    KEY `FK_transactionmember` (`MemberID`),
    CONSTRAINT `FK_transactionmember` FOREGIN KEY (`MemberID`) REFERENCES `member` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=latin1