1
\$\begingroup\$

I would like your thoughts on my database structure. I am kind of a rookie but I am trying. All of your suggestions, alternatives, ideas, additions are welcome. Thank you.

CREATE TABLE `buy` (
  `PurchaseID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `PurchaseTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `CashMachineCode` int(10) unsigned NOT NULL,
  PRIMARY KEY (`PurchaseID`),
  KEY `CashMachineCode` (`CashMachineCode`),
  CONSTRAINT `buy_ibfk_1` FOREIGN KEY (`CashMachineCode`) REFERENCES `cashmachine` (`CashMachineCode`) ON DELETE CASCADE ON UPDATE CASCADE
)



CREATE TABLE `buying` (
  `CustomerID` int(10) unsigned NOT NULL,
  `PurchaseID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`CustomerID`,`PurchaseID`),
  KEY `PurchaseID` (`PurchaseID`),
  CONSTRAINT `buying_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`CreditCardCode`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `buying_ibfk_2` FOREIGN KEY (`PurchaseID`) REFERENCES `buy` (`PurchaseID`) ON DELETE CASCADE ON UPDATE CASCADE
)



CREATE TABLE `shift` (
  `CashierCode` int(10) unsigned NOT NULL,
  `CashMachineCode` int(10) unsigned NOT NULL,
  `ShiftStart` time NOT NULL,
  `ShiftEnd` time DEFAULT NULL,
  PRIMARY KEY (`CashierCode`,`CashMachineCode`,`ShiftStart`),
  KEY `CashMachineCode` (`CashMachineCode`),
  CONSTRAINT `shift_ibfk_1` FOREIGN KEY (`CashierCode`) REFERENCES `employee` (`EmployeeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `shift_ibfk_2` FOREIGN KEY (`CashMachineCode`) REFERENCES `cashmachine` (`CashMachineCode`) ON DELETE CASCADE ON UPDATE CASCADE
) 


CREATE TABLE `availability` (
  `ShopID` int(10) unsigned NOT NULL,
  `ProductID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ProductID`,`ShopID`),
  KEY `ShopID` (`ShopID`),
  CONSTRAINT `availability_ibfk_1` FOREIGN KEY (`ShopID`) REFERENCES `shop` (`ShopID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `availability_ibfk_2` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`) ON DELETE CASCADE ON UPDATE CASCADE
)


CREATE TABLE `director` (
  `EmployeeID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`EmployeeID`),
  CONSTRAINT `director_ibfk_1` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE CASCADE ON UPDATE CASCADE
)


CREATE TABLE `WorksOn` (
  `EmployeeID` int(10) unsigned NOT NULL,
  `ShopID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`EmployeeID`),
  KEY `ShopID` (`ShopID`),
  CONSTRAINT `WorksOn_ibfk_1` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `WorksOn_ibfk_2` FOREIGN KEY (`ShopID`) REFERENCES `shop` (`ShopID`) ON DELETE CASCADE ON UPDATE CASCADE
)


CREATE TABLE `shop` (
  `ShopID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(45) DEFAULT NULL,
  `Address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ShopID`)
)


CREATE TABLE `customer` (
  `CreditCardCode` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CustomerName` varchar(45) DEFAULT NULL,
  `CustomerSurname` varchar(45) DEFAULT NULL,
  `Address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`CreditCardCode`)
)


CREATE TABLE `contains` (
  `PurchaseID` int(10) unsigned NOT NULL,
  `ProductID` int(10) unsigned NOT NULL,
  `Quantity` int(10) unsigned NOT NULL,
  `ProductPrice` decimal(10,2) unsigned NOT NULL,
  PRIMARY KEY (`PurchaseID`,`ProductID`),
  KEY `ProductID` (`ProductID`),
  CONSTRAINT `contains_ibfk_1` FOREIGN KEY (`PurchaseID`) REFERENCES `buy` (`PurchaseID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `contains_ibfk_2` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`) ON DELETE CASCADE ON UPDATE CASCADE
)


CREATE TABLE `product` (
  `ProductID` int(10) unsigned NOT NULL,
  `Description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ProductID`)
)


CREATE TABLE `cashmachine` (
  `CashMachineCode` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Number` int(11) NOT NULL,
  `ShopID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`CashMachineCode`),
  KEY `ShopID` (`ShopID`),
  CONSTRAINT `cashmachine_ibfk_1` FOREIGN KEY (`ShopID`) REFERENCES `shop` (`ShopID`) ON DELETE CASCADE ON UPDATE CASCADE
)


CREATE TABLE `cashier` (
  `EmployeeID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`EmployeeID`),
  CONSTRAINT `cashier_ibfk_1` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`) ON DELETE CASCADE ON UPDATE CASCADE
)


CREATE TABLE `employee` (
  `EmployeeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `EmployeeName` varchar(45) DEFAULT NULL,
  `EmployeeSurname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`EmployeeID`)
)
\$\endgroup\$
3
\$\begingroup\$

Looking at what you have so far, it seems your tables logically fall into two groups:

  1. entity tables
  2. relationship tables.

It may be easier to read if you define all the entity tables first, then all the relationship tables.

In general, your entity tables are clear as written (employee, cashmachine, product, shop, customer, and buy<--you should rename buy as purchase so it's clearly a noun). The employee table is a particularly clear example. The name of the table tells me that it is a table of employees. Its primary key is sensibly called EmployeeID, and is auto-incrementing.

The change I would recommend is to simplify your relationship tables. In particular, some of the relationships you're defining actually don't need any separate table. For example, cashier and director could just be boolean columns added to the employee table. Similarly, if Purchases-to-Customers is a many-to-one relationship, then CustomerID should be an added column to purchase table, eliminating the need for the 'buying' cross table.

Where a cross table is necessary (I see Products-to-Shops availability is a many-to-many relationship), try to rename it to include the name of at least one of the related entities, such as product_availability instead of availability and purchase_items instead of contains.

\$\endgroup\$
5
  • \$\begingroup\$ Can you please edit my table end simplify it? At least one or two tables. Thank you very much for your help. \$\endgroup\$
    – EnexoOnoma
    Jun 20 '11 at 20:23
  • \$\begingroup\$ I understand, the tables cashier and director are defined to guarantee that only one person can hold the respective attribute (cashier or director). Probably there was no better way to enforce that on the database level. \$\endgroup\$
    – Andriy M
    Jun 22 '11 at 16:54
  • \$\begingroup\$ @Andriy -- As written there could be any number of people listed in the cashier and director tables. In real life it might make sense to have many cashiers and one director. With boolean director column this could sort of be enforced with add column director bool unique, using True for the only director and NULL for everyone else. \$\endgroup\$
    – krubo
    Jun 30 '11 at 2:52
  • \$\begingroup\$ Don't know about MySQL, but such a trick would definitely not work in SQL Server. If there's the unique constraint defined on the column, all its contents must be unique, which means there could only be possible just one NULL. Are you positive it would work in MySQL? As for the many cashiers, well, judging from the schema, this particular company seemed to require there to be just one. \$\endgroup\$
    – Andriy M
    Jun 30 '11 at 4:40
  • \$\begingroup\$ @Andriy -- Interesting. I successfully tested bool unique in MySQL, but you're right that it breaks in MSSQL. In MSSQL it would be better to use a table-level check constraint, which lets you place any constraint criteria you want, but is not supported my MySQL. \$\endgroup\$
    – krubo
    Jun 30 '11 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.