...

View Full Version : Database design for Old Pedant Fou-Lu n others



Arcticwarrio
03-16-2013, 01:03 AM
Proposed table designs as requested - this any good?

not too sure what all the links will be yet, but they have the same names in each table:


table - address
# Column Type Collation Null Default Extra
1 AddressID int(11) No None AUTO_INCREMENT <= PRIMARY
2 CustomerID int(11) Yes NULL
3 SupplierID int(11) Yes NULL
4 Address1 varchar(128) utf8mb4_unicode_ci No None
5 Address2 varchar(128) utf8mb4_unicode_ci Yes NULL
6 Town varchar(128) utf8mb4_unicode_ci Yes NULL
7 County varchar(128) utf8mb4_unicode_ci Yes NULL
8 Postcode varchar(128) utf8mb4_unicode_ci No None
9 AddressName varchar(128) utf8mb4_unicode_ci No None
10 AddressType varchar(128) utf8mb4_unicode_ci No None


table - customers
# Column Type Collation Null Default Extra
1 CustomerID int(11) No None AUTO_INCREMENT <= PRIMARY
2 FirstName varchar(128) utf8mb4_unicode_ci Yes NULL
3 LastName varchar(128) utf8mb4_unicode_ci Yes NULL
4 emailaddress varchar(256) utf8mb4_unicode_ci Yes NULL

table - menu
# Column Type Collation Null Default Extra
1 FldMenuID int(11) No None AUTO_INCREMENT <= PRIMARY
2 FldGroup int(11) Yes NULL
3 FldParent int(11) Yes NULL
4 FldName varchar(128) utf8mb4_unicode_ci Yes NULL
5 FldLink varchar(128) utf8mb4_unicode_ci Yes NULL
6 FldAlt varchar(128) utf8mb4_unicode_ci Yes NULL
7 FldTitle varchar(128) utf8mb4_unicode_ci Yes NULL
8 FldCode varchar(24) utf8mb4_unicode_ci Yes NULL
9 FldActive int(2) No 1

table - nextnumber
# Column Type Collation Null Default Extra
1 NumberID int(11) No None AUTO_INCREMENT <= PRIMARY
2 NumberType varchar(32) utf8mb4_unicode_ci No None
3 NumberValue int(11) No None

NumberID NumberType NumberValue
1 OrderNo 200001
2 DeliveryNo 600001
3 InvoiceNo 100001
4 PurchaseNo 300001

table - orders
# Column Type Collation Null Default Extra
1 OrderID int(11) No None AUTO_INCREMENT <= PRIMARY
2 OrderNumber int(11) No None
3 CustomerID int(11) No None
4 VATID int(11) No None
5 AddressID int(11) No None
6 OrderDate timestamp No CURRENT_TIMESTAMP
7 DeliveryDate datetime No None
8 UserID int(11) No None
9 OrderStatus varchar(24) utf8mb4_unicode_ci No None

table - sales
# Column Type Collation Null Default Extra
1 SalesID int(11) No None AUTO_INCREMENT <= PRIMARY
2 OrderID int(11) No None
3 StockID int(11) No None
4 SaleQty int(11) No None
5 SalePrice float(11,2) No None

table - stock
# Column Type Collation Null Default Extra
1 StockID int(11) No None AUTO_INCREMENT <= PRIMARY
2 StockCode varchar(32) utf8mb4_unicode_ci No None
3 StockDesc varchar(64) utf8mb4_unicode_ci No None
4 StockQty int(11) No None
5 StockCost float(11,2) No None
6 StockSell float(11,2) No None
7 StockNotes varchar(128) utf8mb4_unicode_ci No None

table - users
# Column Type Collation Null Default Extra
1 FldUserID int(11) No None AUTO_INCREMENT <= PRIMARY
2 FldUsername varchar(24) utf8mb4_unicode_ci No None
3 FldFirstName varchar(24) utf8mb4_unicode_ci No None
4 FldLastName varchar(24) utf8mb4_unicode_ci No None
5 FldEmail varchar(24) utf8mb4_unicode_ci No None
6 FldGroup varchar(24) utf8mb4_unicode_ci No None
7 FldAccess int(24) No 1
8 FldStrikes int(24) No 0
9 FldIPADDY varchar(24) utf8mb4_unicode_ci Yes NULL
10 FldMobile varchar(24) utf8mb4_unicode_ci Yes NULL
11 FldStatus int(11) Yes NULL
12 FldPassword varchar(128) utf8mb4_unicode_ci No None
13 FldCreated datetime No None
14 FldLastLog timestamp on update CURRENT_TIMESTAMP Yes NULL ON UPDATE CURRENT_TIMESTAMP

table - vatrates
# Column Type Collation Null Default Extra
1 VATID int(11) No None AUTO_INCREMENT <= PRIMARY
2 VATNAME varchar(32) utf8mb4_unicode_ci No None
3 VATPERC float(11,2) No None



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum