Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Web Coding › Quick database question
New Posts  All Forums:Forum Nav:

Quick database question

post #1 of 5
Thread Starter 
So I'm in the process of creating my first database for school
Warning: Spoiler! (Click to show)
.

/*
Item Table #1
*/
CREATE TABLE Item
(ItemID INT(5) NOT NULL AUTO_INCREMENT,
iDescription VARCHAR(200),
iCategory CHAR(2),
PRIMARY KEY (ItemID));
/*
Insert Item row #1
*/
INSERT INTO Item
VALUES
(00001,'T-Shirt','AP')
/*
Insert Item row #2
*/
INSERT INTO Item
VALUES
(00002,'T-Shirt','AP')
/*
Insert Item row #3
*/
INSERT INTO Item
VALUES
(00003,'T-Shirt','AP')

/*
********************************************************************************************************
*/

/*
Inventory Table #2
*/
CREATE TABLE Inventory
(InventoryID INT(3) NOT NULL,
ItemID INT,
inPrice DECIMAL(6,2),
inColor VARCHAR(10),
inSize CHAR(2),
Quantity INT(3),
PRIMARY KEY (InventoryID),
FOREIGN KEY (ItemID) REFERENCES Item(ItemID));
/*
Insert Inventory row #1-3 Grey T-Shirts SM, MD, LG
*/
INSERT INTO Inventory
VALUES
(001,001,24.99,10,'Grey','SM')
INSERT INTO Inventory
VALUES
(002,001,24.99,10,'Grey','MD')
INSERT INTO Inventory
VALUES
(003,001,24.99,10,'Grey','LG')
/*
Insert Inventory row #4-6 Black T-Shirts SM, MD, LG
*/
INSERT INTO Inventory
VALUES
(004,001,24.99,0,'White','SM')
INSERT INTO Inventory
VALUES
(005,001,24.99,0,'White','MD')
INSERT INTO Inventory
VALUES
(006,001,24.99,0,'White','LG')
/*
********************************************************************************************************
*/

/*
ShipmentsReceived Table #3
*/
CREATE TABLE Shipments
(ShipID INT(5) NOT NULL AUTO_INCREMENT,
InventoryID INT,
sDate DATE,
sQuantityOrdered INT(10),
sQuantityReceived INT(10),
PRIMARY KEY (ShipID),
FOREIGN KEY (InventoryID) REFERENCES Inventory(InventoryID));

/*
Insert Shipments row #1
*/
INSERT INTO Shipments
VALUES
(00001,1,'2013-01-16',10,10)
/*
Insert Shipments row #2
*/
INSERT INTO Shipments
VALUES
(00002,2,'2013-01-16',10,10)
/*
Insert Shipments row #3
*/
INSERT INTO Shipments
VALUES
(00003,3,'2013-01-16',10,10)

/*
********************************************************************************************************
*/

/*
Customer Table #4
*/
CREATE TABLE Customer
(CustID INT(5) NOT NULL AUTO_INCREMENT,
cFname VARCHAR(15) NOT NULL,
cMI CHAR(1) NOT NULL,
cLname VARCHAR(15) NOT NULL,
cAddress VARCHAR(25),
cCity VARCHAR(25),
cState CHAR(2),
cZip CHAR(5),
cPhone CHAR(10),
cDOB DATE,
cEmail VARCHAR(30),
PRIMARY KEY(CustID));

/*
Insert Customer row #1
*/
INSERT INTO Customer
VALUES
(00001,'Eli','E','Adams','123 Main St #125A','Carlsbad','CA','92008',NULL,NULL,'EAdams@gmail.com')
/*
Insert Customer row #2
*/
INSERT INTO Customer
VALUES
(00002,'Amanda','A','Almond','351 W 6th Ave','Carlsbad','CA','92008','7608154555','1985-03-12','EAdams@gmail.com')
/*
Insert Customer row #3
*/
INSERT INTO Customer
VALUES
(00003,'John','F','FitzPatrick','83 N River Dr SteA','Carlsbad','CA','92008','7606053555',NULL,'JohnF89@gmail.com')

/*
********************************************************************************************************
*/

/*
Order Table #5
*/
CREATE TABLE Orders
(OrderID INT(5) NOT NULL AUTO_INCREMENT,
CustID INT,
oPayment CHAR(2),
oSource VARCHAR(10),
oDate DATE,
PRIMARY KEY (OrderID),
FOREIGN KEY (CustID) REFERENCES Customer(CustID));

/*
Insert Orders row #1
*/
INSERT INTO Orders
VALUES
(00001,00001,'CR','Website','2013-01-22')

/*
Insert Orders row #2
*/
INSERT INTO Orders
VALUES
(00002,00002,'CH','In-Store','2013-01-24')

/*
Insert Orders row #3
*/
INSERT INTO Orders
VALUES
(00003,00003,'CR','Website','2013-01-22')
/*
********************************************************************************************************
*/


/*
OrderDetail Table #2
*/
CREATE TABLE OrderDetail
(OrderID INT,
InventoryID INT,
Quantity INT(10),
Price DECIMAL(6,2),
PRIMARY KEY (OrderID, InventoryID));

If you look at the spoiler above, I have all my Tables created, the last one is the order detail table. My issue, is that This tables primary key is made up of 2 composite keys from different Tables. (Item table and Inventory table)
As this is my first DB i have no idea how to declare that.

CREATE TABLE OrderDetail
(OrderID INT,
InventoryID INT,
Quantity INT(10),
Price DECIMAL(6,2),
PRIMARY KEY (OrderID, InventoryID));

or maybe PRIMARY KEY Orders, Inventory(OrderID, InventoryID)); ??

That's what i have now, i haven't tested it because i don't think it's right,

Any info will be greatly appreciated and +rep to anyone that helps
Edited by StormProtocol - 1/29/13 at 10:01pm
LikeCorsairMuch
(29 items)
 
  
CPUMotherboardGraphicsRAM
intel i7 2600k Asus p8Z68 V LE Asus GTX 670 Corsair Vengeance 16GB 1600 
Hard DriveHard DriveHard DriveHard Drive
Corsair Force 3 120GB SSD Corsair Force 3 GT 240GB SSD (Red) WD 2TB Caviar Black SeaGate 500GB HDD 
Optical DriveOptical DriveCoolingCooling
Asus DVD-RW LG Blu-Ray RW w/ lightscribe Corsair H100 Aero cool Shark Fan 120mm 
CoolingCoolingCoolingCooling
Aero cool Shark Fan 120mm Aero cool Shark Fan 120mm Aero cool Shark Fan 120mm Aero cool Shark Fan 140mm 
CoolingCoolingOSMonitor
Aero cool Shark Fan 140mm Aero cool Shark Fan 140mm Windows 7 Ultimate 64bit Asus  
MonitorMonitorKeyboardPower
Asus Asus Corsair k90 Mechanical Corsair AX1200W 
CaseMouseMouse PadAudio
Corsair 800D Razer Naga Smiley Face Sony optical HTS 
Audio
Corsair 2500 Wireless Headset 
  hide details  
Reply
LikeCorsairMuch
(29 items)
 
  
CPUMotherboardGraphicsRAM
intel i7 2600k Asus p8Z68 V LE Asus GTX 670 Corsair Vengeance 16GB 1600 
Hard DriveHard DriveHard DriveHard Drive
Corsair Force 3 120GB SSD Corsair Force 3 GT 240GB SSD (Red) WD 2TB Caviar Black SeaGate 500GB HDD 
Optical DriveOptical DriveCoolingCooling
Asus DVD-RW LG Blu-Ray RW w/ lightscribe Corsair H100 Aero cool Shark Fan 120mm 
CoolingCoolingCoolingCooling
Aero cool Shark Fan 120mm Aero cool Shark Fan 120mm Aero cool Shark Fan 120mm Aero cool Shark Fan 140mm 
CoolingCoolingOSMonitor
Aero cool Shark Fan 140mm Aero cool Shark Fan 140mm Windows 7 Ultimate 64bit Asus  
MonitorMonitorKeyboardPower
Asus Asus Corsair k90 Mechanical Corsair AX1200W 
CaseMouseMouse PadAudio
Corsair 800D Razer Naga Smiley Face Sony optical HTS 
Audio
Corsair 2500 Wireless Headset 
  hide details  
Reply
post #2 of 5
My understanding of databases is you can only have one primary key. Your best way would be to have a record number and then have that ascending from 0 and then use that.

Comps
post #3 of 5
I'm pretty sure what you have PRIMARY KEY (OrderID, InventoryID) should work.
My Rig
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel i7 2600k Asus Maximus IV Extreme evga gtx 670 ftw G.Skill Ripjaws X 
Hard DriveOptical DriveCoolingOS
Western Digital Caviar Black 1TB 7200 RPM 64MB ... LITE-ON Black 12X BD-R 2X BD-RE 16X DVD+R 12X D... Noctua NH-D14 Windows 7 Professional x64 
MonitorKeyboardPowerCase
DELL Ultrasharp 2410 Saitek Cyborg Corsair HX850 Cooler Master HAF-X 
Mouse
logitech g500 
  hide details  
Reply
My Rig
(13 items)
 
  
CPUMotherboardGraphicsRAM
Intel i7 2600k Asus Maximus IV Extreme evga gtx 670 ftw G.Skill Ripjaws X 
Hard DriveOptical DriveCoolingOS
Western Digital Caviar Black 1TB 7200 RPM 64MB ... LITE-ON Black 12X BD-R 2X BD-RE 16X DVD+R 12X D... Noctua NH-D14 Windows 7 Professional x64 
MonitorKeyboardPowerCase
DELL Ultrasharp 2410 Saitek Cyborg Corsair HX850 Cooler Master HAF-X 
Mouse
logitech g500 
  hide details  
Reply
post #4 of 5
"My issue, is that This tables primary key is made up of 2 composite keys from different Tables. "

They are FOREIGN KEYS. You should use foreign keys instead of primary keys.

I can't really help you writing it because I only wrote my DB scripts to learn it, now I mostly use tools like mysql workbench tongue.gif
post #5 of 5
You can have more than one column as your primary key, but like EduFurtado said, if those values come from other tables then make them foreign keys, and set a primary key specific to this third table. Just make the PK ID or something and have those other keys as FK. You should be able to set a constrain for this table so that those FK need to be present in order to insert a row in your third table.
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Web Coding
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Web Coding › Quick database question