Re: Database Schema

[Rick James]

> 3. I amin Australia and as such our States are 3 char abbreviated and our postcodes are a set of four numbers;

Sorry, my incorrect assumption.

5. I have used BLOB as this is a field where my wife types brief notes that varies signicicantly in length depending upon the client. It is also a field that gets added to over time. Please advise if I should use a diffent data type fo rthis purpose.

TEXT is for text; BLOB is for things like images. Either is limited to 64K bytes. MEDIUMTEXT is limited to 4M bytes.

> 6. Price - I will check the data type - my intension is allow a value not ever expected to exceed $1,000 - it will always be in $AU where we have 99 cents to one dollar - I think this is OK.

DECIMAL(2) allows 0 to 99 -- it does not allow for any decimal places, nor allow going over 99 dollars. Perhaps you need DECIMAL(5,2), which would allow 0.00 to 999.99. Check the manual.

> I will update my tables and develop my select statements as well as provide a schema diagram from MySQL Workbench if you can advise how I can include it on this forum.

Do you intend to repeatedly type the same queries in Workbench? That seems clumsy. Normally an application is written in some language like PHP or VB, complete with web pages and forms to fill in. That is, the "user" (your wife, or her bookkeeper (you?)) never touches SQL.

There are a lot of free pastit services for things like the diagram. (I don't get much out of such; I go for the CREATE TABLEs.)