What do you think of my database structure?

Oskare100 · #1 (**permalink**) 12-26-06, 06:09 PM

Hello,
I'm building a download site for paid members only and I log, at least according to me, quite a lot of things. I do also have the logs spread out on several tables so I'm wondering if I maybe have a "strange" database structure that will be hard to work with.. Here is it;

Code:

-- 
-- Table structure for table `downl_compl_logs`
-- 

CREATE TABLE `downl_compl_logs` (
  `downl_id` int(11) NOT NULL auto_increment,
  `downl_file_id` varchar(40) NOT NULL default '',
  `downl_file_fullname` varchar(30) NOT NULL default '',
  `downl_ip` varchar(30) NOT NULL default '',
  `downl_user` varchar(50) NOT NULL default '',
  `downl_refer` varchar(20) NOT NULL default '',
  `downl_status` varchar(30) NOT NULL default '',
  `downl_date` varchar(30) NOT NULL default '',
  `downl_timestamp` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`downl_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `downl_fail_logs`
-- 

CREATE TABLE `downl_fail_logs` (
  `downl_id` int(11) NOT NULL auto_increment,
  `downl_file_id` varchar(40) NOT NULL default '',
  `downl_file_fullname` varchar(30) NOT NULL default '',
  `downl_ip` varchar(30) NOT NULL default '',
  `downl_user` varchar(50) NOT NULL default '',
  `downl_refer` varchar(20) NOT NULL default '',
  `downl_status` varchar(30) NOT NULL default '',
  `downl_date` varchar(30) NOT NULL default '',
  `downl_timestamp` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`downl_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `files`
-- 

CREATE TABLE `files` (
  `file_id` int(11) NOT NULL auto_increment,
  `file_pack` varchar(50) NOT NULL default '',
  `file_pack_cat` varchar(50) NOT NULL default '',
  `file_cat` varchar(50) NOT NULL default '',
  `file_name` varchar(100) NOT NULL default '',
  `file_desc` text NOT NULL,
  `file_fullname` varchar(100) NOT NULL default '',
  `file_downloads` varchar(11) NOT NULL default '',
  PRIMARY KEY  (`file_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

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

-- 
-- Table structure for table `ip_logs`
-- 

CREATE TABLE `ip_logs` (
  `ip_id` int(11) NOT NULL auto_increment,
  `ip_ip` varchar(30) NOT NULL default '',
  `ip_user` varchar(50) NOT NULL default '',
  `ip_latest_date` varchar(30) NOT NULL default '',
  `ip_latest_timestamp` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`ip_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `items` (Items that a user can buy)
-- 

CREATE TABLE `items` (
  `item_id` int(11) NOT NULL auto_increment,
  `item_name` varchar(100) NOT NULL default '',
  `item_identifi` varchar(100) NOT NULL default '',
  `item_price` varchar(30) NOT NULL default '',
  `item_file_name` varchar(100) NOT NULL default '',
  `item_file_pack` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `login_logs`
-- 

CREATE TABLE `login_logs` (
  `login` int(11) NOT NULL auto_increment,
  `login_user` varchar(50) NOT NULL default '',
  `login_ip` varchar(30) NOT NULL default '',
  `login_date` varchar(30) NOT NULL default '',
  `login_timestamp` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`login`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `sales`
-- 

CREATE TABLE `sales` (
  `invoice_id` int(10) NOT NULL auto_increment,
  `receiver_email` varchar(60) NOT NULL default '',
  `item_name` varchar(100) NOT NULL default '',
  `item_number` varchar(18) NOT NULL default '',
  `quantity` varchar(6) NOT NULL default '',
  `payment_status` varchar(10) NOT NULL default '',
  `pending_reason` varchar(10) NOT NULL default '',
  `payment_date` varchar(25) NOT NULL default '',
  `mc_gross` varchar(20) NOT NULL default '',
  `mc_fee` varchar(20) NOT NULL default '',
  `tax` varchar(20) NOT NULL default '',
  `mc_currency` varchar(4) NOT NULL default '',
  `txn_id` varchar(20) NOT NULL default '',
  `txn_id_refund` varchar(20) NOT NULL default '',
  `txn_type` varchar(10) NOT NULL default '',
  `payment_type` varchar(10) NOT NULL default '',
  `notify_version` varchar(10) NOT NULL default '',
  `verify_sign` varchar(10) NOT NULL default '',
  `referrer_id` varchar(10) NOT NULL default '',
  `memo` varchar(255) NOT NULL default '',
  `for_auction` varchar(20) NOT NULL default '',
  `auction_buyer_id` varchar(64) NOT NULL default '',
  `auction_closing_date` varchar(21) NOT NULL default '',
  `auction_multi_item` varchar(20) NOT NULL default '',
  `account_username` varchar(50) NOT NULL default '',
  `account_password` varchar(20) NOT NULL default '',
  `received_timestamp` varchar(20) NOT NULL default '',
  `first_name` varchar(30) NOT NULL default '',
  `last_name` varchar(40) NOT NULL default '',
  `  	address_street` varchar(50) NOT NULL default '',
  `address_city` varchar(30) NOT NULL default '',
  `address_state` varchar(30) NOT NULL default '',
  `address_zip` varchar(20) NOT NULL default '',
  `address_country` varchar(30) NOT NULL default '',
  `address_status` varchar(11) NOT NULL default '',
  `payer_email` varchar(60) NOT NULL default '',
  `  	payer_status` varchar(10) NOT NULL default '',
  `mark_paid` varchar(20) NOT NULL default '',
  `mark_shipped` varchar(20) NOT NULL default '',
  `mark_feedback` varchar(30) NOT NULL default '',
  `feedback_received` varchar(100) NOT NULL default '',
  `feedback_left` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`invoice_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `user_perm` (the files the user has permission to download)
-- 

CREATE TABLE `user_perm` (
  `perm_id` int(11) NOT NULL auto_increment,
  `perm_user` varchar(50) NOT NULL default '',
  `file_pack` varchar(30) NOT NULL default '',
  `file_name` varchar(100) NOT NULL default '',
  `perm_date` varchar(30) NOT NULL default '',
  `perm_timestamp` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`perm_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `users`
-- 

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL auto_increment,
  `username` varchar(100) NOT NULL default '',
  `password` varchar(40) NOT NULL default '',
  `payer_email` varchar(100) NOT NULL default '',
  `ebay_userid` varchar(50) NOT NULL default '',
  `num_downloads` varchar(10) NOT NULL default '',
  `num_logins` varchar(10) NOT NULL default '',
  `num_ips` varchar(30) NOT NULL default '',
  `num_purchases` varchar(10) NOT NULL default '',
  `address_street` varchar(50) NOT NULL default '',
  `address_city` varchar(30) NOT NULL default '',
  `address_state` varchar(30) NOT NULL default '',
  `address_zip` varchar(20) NOT NULL default '',
  `address_country` varchar(30) NOT NULL default '',
  `address_status` varchar(11) NOT NULL default '',
  `payer_status` varchar(10) NOT NULL default '',
  `first_name` varchar(30) NOT NULL default '',
  `last_name` varchar(40) NOT NULL default '',
  `latest_login_date` varchar(30) NOT NULL default '',
  `latest_login_timestamp` varchar(30) NOT NULL default '',
  `created_date` varchar(30) NOT NULL default '',
  `created_timestamp` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Feedback is appreciated : )

Thanks in advance,
/Oskar

Nico · #2 (**permalink**) 12-26-06, 06:26 PM

The IP field doesn't need to be longer than 15 chars, and the timestamp field can be 10/11 chars long. (Assuming you're using PHP's time() function.)

odi · #3 (**permalink**) 12-27-06, 03:29 AM

I think you have a quite random way of naming your attributes.

First I thought you always prefix the tablename, but this is only for some true. Then I thought you want names that are unique over the whole database, but this is also false (e.g. downl_id).

I prefer naming the attriibutes without a prefix, and when you're using them in a SQL-statement you will prefix them with the table like that:

Code:

select u.user_id from users u;

I also think some names are confusing, because I guess the table 'login_logs' is to log who logged in etc. but with the prefix 'login' I would guess that these are login information like username/password etc.

You are using the same attributes in different tables (e.g. item_name in 'sales' and 'items'), but I guess they have the same source. I would propose to to a foreign key from items to sales, so you would have only one attribute in sales "linking" to the items table.

That's all I can see after a quick view of your structure.

Oskare100 · #4 (**permalink**) 12-27-06, 05:52 AM

Really thanks for your feedback,
I've changed some things now, what do you think of this structure;

Code:

-- 
-- Table structure for table `downl_logs`
-- 

CREATE TABLE `downl_logs` (
  `downl_id` int(11) NOT NULL auto_increment,
  `file_id` varchar(40) NOT NULL default '',
  `ip` varchar(30) NOT NULL default '',
  `user_id` int(10) NOT NULL default '0',
  `refer` varchar(20) NOT NULL default '',
  `status` varchar(30) NOT NULL default '',
  `date` varchar(30) NOT NULL default '',
  `timestamp` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`downl_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `file_packs`
-- 

CREATE TABLE `file_packs` (
  `pack_id` int(10) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `desc` text NOT NULL,
  PRIMARY KEY  (`pack_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `files`
-- 

CREATE TABLE `files` (
  `file_id` int(11) NOT NULL auto_increment,
  `pack_id` int(10) NOT NULL default '0',
  `cat` varchar(50) NOT NULL default '',
  `name` varchar(100) NOT NULL default '',
  `desc` text NOT NULL,
  `file_name` varchar(100) NOT NULL default '',
  `num_downloads` int(10) NOT NULL default '0',
  PRIMARY KEY  (`file_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

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

-- 
-- Table structure for table `ip_logs`
-- 

CREATE TABLE `ip_logs` (
  `ip_id` int(11) NOT NULL auto_increment,
  `ip` varchar(30) NOT NULL default '',
  `user_id` int(10) NOT NULL default '0',
  `latest_date` varchar(30) NOT NULL default '',
  `latest_timestamp` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`ip_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `items`
-- 

CREATE TABLE `items` (
  `item_id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `identifi` varchar(100) NOT NULL default '',
  `price` varchar(30) NOT NULL default '',
  `file_name` varchar(100) NOT NULL default '',
  `file_pack` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `login_logs`
-- 

CREATE TABLE `login_logs` (
  `login_id` int(11) NOT NULL auto_increment,
  `user_id` int(10) NOT NULL default '0',
  `ip` varchar(30) NOT NULL default '',
  `date` varchar(30) NOT NULL default '',
  `timestamp` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`login_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `sales`
-- 

CREATE TABLE `sales` (
  `invoice_id` int(10) NOT NULL auto_increment,
  `user_id` int(10) NOT NULL default '0',
  `receiver_email` varchar(60) NOT NULL default '',
  `auction_item_name` varchar(100) NOT NULL default '',
  `auction_item_number` varchar(18) NOT NULL default '',
  `quantity` varchar(6) NOT NULL default '',
  `payment_status` varchar(10) NOT NULL default '',
  `pending_reason` varchar(10) NOT NULL default '',
  `payment_date` varchar(25) NOT NULL default '',
  `mc_gross` varchar(20) NOT NULL default '',
  `mc_fee` varchar(20) NOT NULL default '',
  `tax` varchar(20) NOT NULL default '',
  `mc_currency` varchar(4) NOT NULL default '',
  `txn_id` varchar(20) NOT NULL default '',
  `txn_id_refund` varchar(20) NOT NULL default '',
  `txn_type` varchar(10) NOT NULL default '',
  `payment_type` varchar(10) NOT NULL default '',
  `notify_version` varchar(10) NOT NULL default '',
  `verify_sign` varchar(10) NOT NULL default '',
  `referrer_id` varchar(10) NOT NULL default '',
  `memo` varchar(255) NOT NULL default '',
  `for_auction` varchar(20) NOT NULL default '',
  `auction_buyer_id` varchar(64) NOT NULL default '',
  `auction_closing_date` varchar(21) NOT NULL default '',
  `auction_multi_item` varchar(20) NOT NULL default '',
  `account_username` varchar(50) NOT NULL default '',
  `account_password` varchar(20) NOT NULL default '',
  `received_timestamp` varchar(20) NOT NULL default '',
  `received_date` varchar(30) NOT NULL default '',
  `mark_paid` varchar(20) NOT NULL default '',
  `mark_shipped` varchar(20) NOT NULL default '',
  `mark_feedback` varchar(30) NOT NULL default '',
  `feedback_received` varchar(100) NOT NULL default '',
  `feedback_left` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`invoice_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `user_perm`
-- 

CREATE TABLE `user_perm` (
  `perm_id` int(11) NOT NULL auto_increment,
  `user_id` int(10) NOT NULL default '0',
  `pack_id` int(10) NOT NULL default '0',
  `file_id` int(10) NOT NULL default '0',
  `date` varchar(30) NOT NULL default '',
  `timestamp` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`perm_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

-- 
-- Table structure for table `users`
-- 

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL auto_increment,
  `active` varchar(10) NOT NULL default '',
  `username` varchar(100) NOT NULL default '',
  `password` varchar(40) NOT NULL default '',
  `email` varchar(100) NOT NULL default '',
  `ebay_userid` varchar(50) NOT NULL default '',
  `num_downloads` int(5) NOT NULL default '0',
  `num_logins` int(5) NOT NULL default '0',
  `num_ips` int(5) NOT NULL default '0',
  `num_purchases` int(5) NOT NULL default '0',
  `first_name` varchar(30) NOT NULL default '',
  `last_name` varchar(50) NOT NULL default '',
  `payer_status` varchar(10) NOT NULL default '',
  `address_street` varchar(50) NOT NULL default '',
  `address_city` varchar(30) NOT NULL default '',
  `address_state` varchar(30) NOT NULL default '',
  `address_zip` varchar(20) NOT NULL default '',
  `address_country` varchar(30) NOT NULL default '',
  `address_status` varchar(11) NOT NULL default '',
  `latest_login_date` varchar(30) NOT NULL default '',
  `latest_login_timestamp` varchar(30) NOT NULL default '',
  `latest_updated` varchar(30) NOT NULL default '',
  `latest_updated_timestamp` varchar(15) NOT NULL default '',
  `created_date` varchar(30) NOT NULL default '',
  `created_timestamp` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

/Oskar

odi · #5 (**permalink**) 12-27-06, 07:56 AM

yeah looks better to me.

How do you now differ between failed and completed downloads? Is this the status attribute?

If you except a lot of data it can be a good solution to split them in two tables as you did in your first darft, but normally the solution with one table is better.

You still have the issue with the item_name, but it's your business

Oskare100 · #6 (**permalink**) 12-27-06, 08:43 AM

Hello,
What about the item_name?

And, about the download logs, I will add about 100 rows per day to those tables and save them for about one year before they are deleted. Do you think I should split it up again or keep it as it is?

Now, this should be the last problem;
I would also like to put files in categories, do you suggest that I create a new table for with the name, for example, "file_cats" (cats as a shortening for categories) and then add;
"cat_id", "name" and "dec" as in the "file_packs" table? Then I would also need to add "cat_id" to the "files" table.

Or do you think that I should do that in another way? All in all, what do you think of the way I'm (at least trying) to organise the files in packs and categories?

/Oskar

Best Regards