Hi Guys
I am trying to create a database application. I have normalised the form used in the application. I then had 23 tables which i then Rationalised, bringing the total number of tables to 15. This seems alot of tables. I have been told i have to optimise them. How would you do that?
Below are the Rationalised Tables:

------------------------------
Customer

Customer Number
Customer Name
Customer Address
Town
Postal Code
Telephone

----------------------------
Sale

CustInvoice No
CustInvoice Date
Customer Number *
Net Total
Vat
Grand Total
Discount Code*

-----------------------------
Staff

Staff No
First Name
Last Name
Street
Postal Code *
Telephone
Date of Birth
Joined
Next Of Kin
Part Time/Full Time
Pay Code *
Special Note

-----------------------------
Purchase Order Invoice

PurchaseInvoice No
PurchaseInvoice Date
Order No *
Vat No
SubTotal
Discount
Vat
Total

-----------------
Discount (2)

Discount Code
Discount

--------------------------
Supplier (12)

Supplier ID
Supplier Name
Address
Postal Code
Telephone
Email
Fax No

---------------------------
Pay Code (23)

Pay Code
Rate of Pay

-------------------------------
Supplier Invoice

PurchaseInvoice No *
Supplier ID *

--------------------------------
Stock Order

PurchaseInvoice No *
Stock No *
Quantity
Cost

---------------------------------
StockSale

CustInvoice No*
Stock Number *
Quantity
Cost

------------------------------------
Stock

Stock No
Description
Unit
Price
In Stock
On Order
Category
Reorder Level

-----------------------------------
Purchase Order


PurchaseOrder No
PurchaseOrder Date
Supplier ID*

----------------------------------------
StockOrder


PurchaseOrder No *
Stock No *
Quantity

-------------------------------------------
CustomerOrderDetails

CustOrder No
CustOrder Date
Customer Number*

-----------------------------------------
Order

CustOrder No
Stock No*
Quantity

What is Rationalization?

Hi
This is the process by which having normalised all the tables you join together those tables with the same Primary Key into one table. Those tables might have slightly different attributes.

You think 15 tables is too many? You'd hate the database I work on then - it currently has 1,412!

If your data is properly normalised then you have exactly the right number of tables.

the number of tables you have is arbitrary. A good design could have 2 or 2,341,123 tables, it depends on the business need.

The only red flag I see is what appears to be quite a few aggregate fields. Do you have a good reason for storing derived data?

To make your final design more useable, you might want to hide multiple tables behind VIEWs. I've done that for some tables on our system just so the end-users don't have to write complex statements with a bunch of JOINs.

i.e. if you have a ADDRESS table that has a country code that needs a look-up to a COUNTRY table, hide that join in a view:

CREATE VIEW ADDRESS_DETAILS AS
SELECT col1, col2, ... coln, COUNTRY_NAME
FROM ADDRESS A, COUNTRY C
WHERE A.COUNTRY_ID = C.COUNTRY_ID;

Then the user can just say:
SELECT * FROM ADDRESS_DETAILS;

Much easier for them. If you like people who need it to be "easy" accessing your data.

Thanks for your replies guys.

Teddy: the aggregate tables were derived from the normalisation process. I dont quite know what you mean by derived data. Explain please.

J Petruk: Cheers for the suggestion.


The reason i wondered about the size is that it is a fairly simple application and following the rules i came up with that number of tables.

Cheers!!

Derived data is exactly that, data which is derived from pre-existing information.

For example:

tblHasDerivedData
-----
someKey
value1
value2
SumOfValue1AndValue2 'BIG NO-NO

anything that can be calculated or "derived" from information t hat already exists in your database generally should not be stored. There are a few times you may want to do it, but not very often.

With that in mind, i'm looking at your Net Total, VAT and Grand total fields wondering if they are derived from pre-existing information.