7 Replies - 1468 Views - Last Post: 30 August 2012 - 04:29 PM Rate Topic: -----

#1 pietomb00  Icon User is offline

  • D.I.C Head

Reputation: 12
  • View blog
  • Posts: 68
  • Joined: 28-June 11

Simple Database design

Posted 30 August 2012 - 05:48 AM

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




Is This A Good Question/Topic? 0
  • +

Replies To: Simple Database design

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6039
  • View blog
  • Posts: 23,441
  • Joined: 23-August 08

Re: Simple Database design

Posted 30 August 2012 - 06:05 AM

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.
Was This Post Helpful? 1
  • +
  • -

#3 pietomb00  Icon User is offline

  • D.I.C Head

Reputation: 12
  • View blog
  • Posts: 68
  • Joined: 28-June 11

Re: Simple Database design

Posted 30 August 2012 - 06:18 AM

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 :whistling: )
Was This Post Helpful? 0
  • +
  • -

#4 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6039
  • View blog
  • Posts: 23,441
  • Joined: 23-August 08

Re: Simple Database design

Posted 30 August 2012 - 06:34 AM

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.
Was This Post Helpful? 3
  • +
  • -

#5 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1002
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: Simple Database design

Posted 30 August 2012 - 06:42 AM

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.
Was This Post Helpful? 3
  • +
  • -

#6 pietomb00  Icon User is offline

  • D.I.C Head

Reputation: 12
  • View blog
  • Posts: 68
  • Joined: 28-June 11

Re: Simple Database design

Posted 30 August 2012 - 08:05 AM

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.
Was This Post Helpful? 0
  • +
  • -

#7 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6039
  • View blog
  • Posts: 23,441
  • Joined: 23-August 08

Re: Simple Database design

Posted 30 August 2012 - 08:31 AM

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.
Was This Post Helpful? 2
  • +
  • -

#8 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Simple Database design

Posted 30 August 2012 - 04:29 PM

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.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1