Optimization help

[ayvegh]

Hi,

I just designed this client billing database:
--
-- Database: `billing`
--

-- --------------------------------------------------------

--
-- Table structure for table `client`
--

CREATE TABLE `client` (
`clientid` int(11) NOT NULL auto_increment,
`creationdate` datetime NOT NULL,
`name` varchar(255) collate utf8_bin NOT NULL,
`urid` varchar(255) collate utf8_bin NOT NULL,
PRIMARY KEY (`clientid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `invoice`
--

CREATE TABLE `invoice` (
`invoiceid` int(11) NOT NULL auto_increment,
`clientid` int(11) NOT NULL,
`created` datetime NOT NULL,
`received` datetime NOT NULL,
PRIMARY KEY (`invoiceid`),
KEY `clientid` (`clientid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `lineitem`
--

CREATE TABLE `lineitem` (
`itemid` int(11) NOT NULL auto_increment,
`invoiceid` int(11) NOT NULL,
`displayorder` int(11) NOT NULL,
`date` datetime default NULL,
`description` varchar(255) collate utf8_bin NOT NULL,
`amount` double(15,2) NOT NULL,
PRIMARY KEY (`itemid`),
KEY `INVOICEID` (`invoiceid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

My questions:

1. Is there a more efficient method for storing any of the data types?
2. Are any of the fields redundant, and/or should some of the fields be redundant (e.g. should clientid be present in all tables?)

I may have more, but for now, those are the two eating away at me. :)

Thanks in advance,

ayvegh

[chazzy]

the only big issue i possibly see is that not every society uses 2 decimal places only for money.

small things would include the lack of FK's between the tables.

[ayvegh]

Well, all transactions will be in U$D, so no worries there.

What's an FK? :confused:

[chazzy]

foreign key

[96turnerri]

`name` varchar(255) collate utf8_bin NOT NULL,
`urid` varchar(255) collate utf8_bin NOT NULL,

A clients name isn't going to be 255 characters long i would suggest varchar(50) at tops (100).

Not sure what `urid` is either, but if it's an ID it should be an INT if not same applies as above is 255 really neccessary?

`displayorder` int(11) NOT NULL,

Should be a SMALLINT you are not going to have billions of items on 1 invoices, probably get away with TINYINT

[ayvegh]

`name` varchar(255) collate utf8_bin NOT NULL,
`urid` varchar(255) collate utf8_bin NOT NULL,

A clients name isn't going to be 255 characters long i would suggest varchar(50) at tops (100).
Switched both to varchar(100)
Not sure what `urid` is either, but if it's an ID it should be an INT if not same applies as above is 255 really neccessary?
Fake (or real, depending on your POV) SEO - the URL is mydomain.tld/billing/URID
The client can access their own billing information from a unique URL - makes things somewhat convenient (powered by htaccess).
`displayorder` int(11) NOT NULL,

Should be a SMALLINT you are not going to have billions of items on 1 invoices, probably get away with TINYINT
Changed to tinyint(3)

Thanks for all your help, 96turnerri!

@chazzy: What are the pros and cons of foreign keys, and what application would you suggest for this situation?

My final goal is to be able to make relational queries (is that even the right way to say it?) to these tables, to keep the system extremely efficient.

[chazzy]

i don't agree w/ making the varchar's smaller. for one, only the amount used is actually stored, so making the column up to 255 chara's isn't going to hurt you. in addition, the data structures are optimized to store values in 2^n -1 blocks, so making it 50 or 100 will actually slow you down, when compared to 255

i don't agree with making the int's smaller. the suggestion to use tinyint is laughable, especially when talking about invoices and billing. same with smallint.

[ayvegh]

i don't agree w/ making the varchar's smaller. for one, only the amount used is actually stored, so making the column up to 255 chara's isn't going to hurt you. in addition, the data structures are optimized to store values in 2^n -1 blocks, so making it 50 or 100 will actually slow you down, when compared to 255

i don't agree with making the int's smaller. the suggestion to use tinyint is laughable, especially when talking about invoices and billing. same with smallint.
Changed back, for lack of a good response (on my part) to your statements. :)

What about my foreign key question(s)?

[ayvegh]

Another question:

Assuming it's possible, what is an example of a single query which can retrieve every lineitem belonging to a particular client, assuming only the clientid is known?

Or does that need two queries?

[chazzy]

i dunno relational queries are more of a design thing, rather than what you might

you're already using innodb which does in fact support FK's so you might as well use them. is it that you don't know what they are? http://en.wikipedia.org/wiki/Foreign_key

your 2nd question:


select * from lineitem li inner join invoice i on li.invoiceid = i.invoiceid inner join client c on i.clientid = c.clientid and c.clientid='your client id'


just as a note, this query would return results faster if there was a foreign key between the given tables on the listed pairings.

[ayvegh]

Would a JOIN statement be helpful in obtaining that type of query and/or result?

I think I understand foreign keys, but what id's would you recommend applying them to here?

Thank you!

[chazzy]

everything that the query you asked for uses.

[96turnerri]

i don't agree with making the int's smaller. the suggestion to use tinyint is laughable, especially when talking about invoices and billing. same with smallint.

Newsflash, the number in the brackets isn't used it's only for display-width, INT(1) and INT(10) are the same, it's the type of int that counts, e.g. SMALLINT, TINYINT or INT.

When you start storing larges amount of data which you selected will be very noticable.

So it's not pointless the only pointless thing was your comment.

[ayvegh]

Newsflash, the number in the brackets isn't used it's only for display-width, INT(1) and INT(10) are the same, it's the type of int that counts, e.g. SMALLINT, TINYINT or INT.

When you start storing larges amount of data which you selected will be very noticable.

So it's not pointless the only pointless thing was your comment.
Whoa, chill man. I didn't know that either, so he's not the only one. :)

So you're saying that I should go back to tinyint(1) for the displayorder?

Thanks for all your help so far!

[chazzy]

Turnerri, trying to flame users will get you nowhere. Especially if they're mods. As you already know there is a downside to every change.

With that said, you may want to look at all of the differences between the int types and how sizes affect them. I also want to point out that the different types are all essentially synonyms for the sizes.