[pietomb00]

Hi all,

I've always struggled a bit with database design but have decided to give it a go on the project I'm working on. I've read Alti's tutorial on normalisation.

I've got this far:

+---------------+
| Items			|
+---------------+
|	ID			|
|	Item		|
|	Date&Price	|
|	Change		|
|	Count		|
+---------------+


+---------------+		+---------------+
| Items			|		| Prices		|
+---------------+		+---------------+
|	ID			|1--|--*|	ID			|
|	Item		|  		|	Price		|
|	Change		|		|	Date		|
|	Count		|		+---------------+		
+---------------+		

I'm trying to log the price of each item every day. In the first one I'd have to add a new column every day, so have moved that into a separate table, is there anywhere to go from there or is that as far as I can (or need to) for such a small database?

I ask because in my second table it will end up being storing a new row for each item every day which could get quite large.
e.g.

ID	Item	change	count				ID		Price	date
1	Cheese	10		3					1		10	22/02/2012
2	Pizza	50		1					1		20	23/02/2012	
										1		30	24/02/2012
										2		30	24/02/2012

[JackOfAllTrades]

I would make the prices table

prices
------
id (pk)
item_id (fk to item.id)
price
date

What is change in the items table?

As far as the size of the table, it shouldn't be that big a deal. You could always create a cron job/scheduled task that purges rows from the [il]prices[/il ] table on the basis of some date, like weekly, monthly, etc.

[pietomb00]

Why would you have the extra Id in the prices table? Is it because each table should have a pk?

Change is meant to be the difference between the previous two days prices (i realise the second one doesn't make sense as there's only one price for it, it was a bad example )

[JackOfAllTrades]

Yes, I think every table should have a PK.

As regards change, it is redundant to store that information in the table. You have the information stored elsewhere, you use a query to calculate the change on demand.

[RudiVisser]

Agree with JackOfAllTrades. As an aside, could I suggest that you use DDT (Database Design Tool) to make your life easier?

Will help you to visualise the database properly without having to draw diagrams, and hopefully have the consequence of speeding up your design process

More on topic, exactly like JOAT said, I think each table should have a primary key. It makes more sense if you're ever needing to link anything to anything, then you can follow the convention of having [table]_id as your foreign key, which can go off to the id field of any other table in the database.

As your database grows, and you're creating more and more database tables to cope with the amount of data you now need to store, you'll see just how much this will help.

[pietomb00]

Just had a quick look at that DDT, it does make life a lot easier, thanks

I will add a primary key and look at a formula for removing that change.

How do I go about making the relationship in MySql? Or is that a whole other can of worms.

[JackOfAllTrades]

create table prices(
    id int not null auto_increment primary key,
    item_id int not null,
    price_date date not null,
    constraint prices_items_fk foreign key(item_id) references items(id) on delete cascade
) ENGINE=InnoDB;

Note I used price_date as the date field, so there's no conflict with the date reserved word. You could use something else instead, if you want. I just try to avoid clashing with reserved words. For foreign keys you should use the InnoDB engine type as well.

[e_i_pi]

As for your concerns about speed, don't worry about it. SELECT queries are extremely quick, and with a few well-placed indexes you should be fine.

At my work, we have a table for a client that has 650000 rows of data. Selecting the entire set takes 23500ms, or 0.036ms per record. Selecting on a non-primary non-unique index that returns 27567 rows takes 1001ms, or 0.026ms per record. It's all about how you SELECT (i.e. - which filtering clauses you use and which JOIN types you use) as well as appropriate and well-placed indexes.

As another example, I have an IP->Country table in my website's database, it has just shy of 100000 rows. When a user logs in, it compares their IP address against the table - each row of the table has the countryID, the beginningIP, and endingIP. I placed a BTree index on the beginningIP and endingIP, and now a search for an IP address of a user takes about 3ms.