20170420_jeffrey - silenceuncrio/diary GitHub Wiki

0925

來解決 0000340: [enhance] every DDNS Server need different info

1100

解掉並且上 code 了

1110

回到 monkeyjj 資料庫的 design

參考 Six-Step Relational Database Design 書上的 Chapter 9 Implementing the design 的 Implementation Considerations

先拿 Owners 這個 table 來開刀

  • Owners
    • (PK) OwnerId
    • Name
    • Email
Table Column Data Type Length Indexed Requuired(Default)
Owners (PK) OwnerId Number NA Yes, Primary Key Yes
Name Character 50 Yes Yes
Email Character 75 No Yes

1135

繼續吧

  • GroupBuyings
    • (PK) GroupBuyingId
    • Name
    • Description
    • DateStarted
    • DateEnded
Table Column Data Type Length Indexed Requuired(Default)
GroupBuyings (PK) GroupBuyingId Number NA Yes, Primary Key Yes
Name Character 50 Yes Yes
Description Character 250 No No
DateStarted Date NA Yes Yes
DateEnded Date NA No Yes

1145

  • Buyers
    • (PK) BuyerId
    • Name
    • Email
    • FBAccount
Table Column Data Type Length Indexed Requuired(Default)
Buyers (PK) BuyerId Number NA Yes, Primary Key Yes
Name Character 50 Yes Yes
Email Character 75 No Yes
FBAccount Character 75 No No
  • GBuyProducts
    • (PK) GBuyProductId
    • Name
    • price
Table Column Data Type Length Indexed Requuired(Default)
GBuyProducts (PK) GBuyProductId Number NA Yes, Primary Key Yes
Name Character 50 Yes Yes
Price Number 12 No Yes(0)

1155

發現自己都把 FK (Foreign Key) 漏掉了

Owners 沒有 FK

GroupBuyings 有一個 FK

  • GroupBuyings
    • (PK) GroupBuyingId
    • Name
    • Description
    • DateStarted
    • DateEnded
    • (FK) OwnerId
Table Column Data Type Length Indexed Requuired(Default)
GroupBuyings (PK) GroupBuyingId Number NA Yes, Primary Key Yes
Name Character 50 Yes Yes
Description Character 250 No No
DateStarted Date NA Yes Yes
DateEnded Date NA No Yes
(FK) OwnerId Number NA Yes, Foreign Key Yes

1320

Buyers 也沒有 FK

繼續 GBuyProducts - 3 個 FK

  • GBuyProducts
    • (PK) GBuyProductId
    • Name
    • price
    • (FK) GBuyingId
    • (FK) GBuyOrderId
    • (FK) ProductId
Table Column Data Type Length Indexed Requuired(Default)
GBuyProducts (PK) GBuyProductId Number NA Yes, Primary Key Yes
Name Character 50 Yes Yes
Price Number 12 No Yes(0)
(FK) GBuyingId Number NA Yes, Foreign Key Yes
(FK) GBuyOrderId Number NA Yes, Foreign Key Yes
(FK) ProductId Number NA Yes, Foreign Key Yes

GBuyOrderId 這個 attribute 是否為 required 讓我很混淆

然後發現我目前的設計無法解決一筆訂單有各種物品以及不同數量的問題

1400

再重新設計一次 GroupBuying 這個 database 吧

Step 1: Discover entities and assign attribute

  • Products
    • (PK) ProductId
    • Name
    • Description
    • Cost
    • BuyingPeriodStart
    • BuyingPeriodEnd
  • Users
    • (PK) UserId
    • UserName
    • Email
    • Password
  • Directors
    • (PK) DirectorId

Step 2: Derive unary and binary relationships

Products Users Directors
Products - - -
Users buy - -
Directors create is -

Step 3: Create simplified Entity-Relationship diagram

image

Step 4: List assertions for all relationships

  • A Product can be bought by many Users
  • A Product must be created by only one Director
  • A User can be a Director
  • A User can buy many Products
  • A Directors can create many Products
  • A Director must be a User

Step 5: Create detailed E-R diagram using assertions

  • A Product can be bought by many Users (0:N)
  • A Product must be created by only one Director (1:1)
  • A User can be a Director (0:1)
  • A User can buy many Products (0:N)
  • A Directors can create many Products (0:N)
  • A Director must be a User (1:1)

image

1325

Step 6: Transform the detailed E-R diagram into an implementable R-M diagram

Step 6-1: Many-to-many relationships

image

1540

Step 6-2: One-to-many relationships

image

1550

Step 6-3: One-to-one relationships

image

1600

Data Dictionary

  • Products
    • (PK) ProductId
    • Name
    • Description
    • Cost
    • BuyingPeriodStart
    • BuyingPeriodEnd
  • Users
    • (PK) UserId
    • UserName
    • Email
    • Password
  • Directors
    • (PK) DirectorId
Table Column Data Type Length Indexed Requuired(Default)
Products (PK) ProductId Number NA Yes, Primary Key Yes
Name Character 50 Yes Yes
Description Character 250 No No
Cost Number 12 No Yes(0)
BuyingPeriodStart Date NA Yes Yes
BuyingPeriodEnd Date NA Yes Yes
(FK) DirectorId Number NA Yes, Foreign Key Yes
Users (PK) UserId Number NA Yes, Primary Key Yes
UserName Character 50 Yes Yes
Email Character 75 No Yes
Password Character 50 No Yes
Orders (PK) OrderId Number NA Yes, Primary Key Yes
(FK) UserId Number NA Yes, Foreign Key Yes
(FK) ProductId Number NA Yes, Foreign Key Yes
DateOrdered Date NA Yes Yes
Quantity Number 6 No Yes(1)
TotalCost Number 12 No Yes(0)
Directors (PK) DirectorId Number NA Yes, Primary Key Yes
(FK) UserId Number NA Yes, Foreign Key Yes

1620

Table Column Data Type Length Indexed Requuired(Default)
Products (PK) ProductId Number NA Yes, Primary Key Yes
Name Character 50 Yes Yes
Description Character 250 No No
Cost Number 12 No Yes(0)
BuyingPeriodStart Date NA Yes Yes
BuyingPeriodEnd Date NA Yes Yes
(FK) DirectorId Number NA Yes, Foreign Key Yes

SQL Commands

CREATE TABLE Products (
  ProductId int(11) NOT NULL AUTO_INCREMENT,
  Name varchar(50) NOT NULL,
  Description varchar(250) DEFAULT NULL,
  Cost int(12) NOT NULL DEFAULT 0,
  BuyingPeriodStart date NOT NULL,
  BuyingPeriodEnd date NOT NULL,
  PRIMARY KEY (ProductId),
  FOREIGN KEY DirectorId (DirectorId)
    REFERENCES Directors (DirectorId) 
    ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX Name (Name),
);

1640

Table Column Data Type Length Indexed Requuired(Default)
Users (PK) UserId Number NA Yes, Primary Key Yes
UserName Character 50 Yes Yes
Email Character 75 No Yes
Password Character 50 No Yes

SQL Commands

CREATE TABLE Users (
  UserId int(11) NOT NULL AUTO_INCREMENT,
  UserName varchar(50) NOT NULL,
  Email varchar(75) NOT NULL,
  Password varchar(50) NOT NULL,
  PRIMARY KEY (UserId),
  INDEX UserName (UserName),
);

1645

Table Column Data Type Length Indexed Requuired(Default)
Orders (PK) OrderId Number NA Yes, Primary Key Yes
(FK) UserId Number NA Yes, Foreign Key Yes
(FK) ProductId Number NA Yes, Foreign Key Yes
DateOrdered Date NA Yes Yes
Quantity Number 6 No Yes(1)
TotalCost Number 12 No Yes(0)

SQL Commands

CREATE TABLE Orders (
  OrderId int(11) NOT NULL AUTO_INCREMENT,
  DateOrdered date NOT NULL,
  Quantity smallint(6) NOT NULL DEFAULT 1,
  TotalCost int(12) NOT NULL DEFAULT 0,
  PRIMARY KEY (OrderId),
  FOREIGN KEY UserId (UserId)
    REFERENCES Users (UserId) 
    ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY ProductId (ProductId)
    REFERENCES Products (ProductId) 
    ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX DateOrdered (DateOrdered),
);

1650

Table Column Data Type Length Indexed Requuired(Default)
Directors (PK) DirectorId Number NA Yes, Primary Key Yes
(FK) UserId Number NA Yes, Foreign Key Yes

SQL Commands

CREATE TABLE Directors (
  DirectorId int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (DirectorId),
  FOREIGN KEY UserId (UserId)
    REFERENCES Users (UserId) 
    ON UPDATE CASCADE ON DELETE RESTRICT,
);

1700

直接使用 home.monkeyjj.idv.tw 的 phpMyAdmin

先建立一個 database - GroupBuying

貼上 SQL Commands

CREATE TABLE Products (
  ProductId int(11) NOT NULL AUTO_INCREMENT,
  Name varchar(50) NOT NULL,
  Description varchar(250) DEFAULT NULL,
  Cost int(12) NOT NULL DEFAULT 0,
  BuyingPeriodStart date NOT NULL,
  BuyingPeriodEnd date NOT NULL,
  PRIMARY KEY (ProductId),
  FOREIGN KEY DirectorId (DirectorId)
    REFERENCES Directors (DirectorId) 
    ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX Name (Name),
);

最後一行的 , 錯了

CREATE TABLE Products (
  ProductId int(11) NOT NULL AUTO_INCREMENT,
  Name varchar(50) NOT NULL,
  Description varchar(250) DEFAULT NULL,
  Cost int(12) NOT NULL DEFAULT 0,
  BuyingPeriodStart date NOT NULL,
  BuyingPeriodEnd date NOT NULL,
  PRIMARY KEY (ProductId),
  FOREIGN KEY DirectorId (DirectorId)
    REFERENCES Directors (DirectorId) 
    ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX Name (Name)
);

出現錯誤

Error
...
#1072 - Key column 'DirectorId' doesn't exist in table

看來還要照順序才行

CREATE TABLE Users (
  UserId int(11) NOT NULL AUTO_INCREMENT,
  UserName varchar(50) NOT NULL,
  Email varchar(75) NOT NULL,
  Password varchar(50) NOT NULL,
  PRIMARY KEY (UserId),
  INDEX UserName (UserName)
);
CREATE TABLE Directors (
  DirectorId int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (DirectorId),
  FOREIGN KEY UserId (UserId)
    REFERENCES Users (UserId) 
    ON UPDATE CASCADE ON DELETE RESTRICT
);

出現錯誤

Error
...
#1072 - Key column 'UserId' doesn't exist in table

1820

覺得要再小修一下 GroupBuying 這個 database

Step 1: Discover entities and assign attribute

  • Users
    • (PK) UserId
    • UserName
    • Email
    • Password
  • Directors
    • (PK) DirectorId
  • Groups
    • (PK) GroupId
    • Name
    • Description
    • PeriodStart
    • PeriodEnd
  • Products
    • (PK) ProductId
    • Name
    • Description
    • Cost

Step 2: Derive unary and binary relationships

Users Directors Groups Products
Users - - - -
Directors is - - -
Groups create - -
Products buy has -
Add a custom footer
⚠️ **GitHub.com Fallback** ⚠️