Re: Help with some relational things.

[Patrick Twohig]

Wow. I can't believe I didn't think of that before. If I'm understanding you correctly, I have a user's table like this.

I have my customer's table like this.
+-------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------------------+------+-----+---------+----------------+
| user_id | mediumint(8) unsigned | | PRI | NULL | auto_increment |
| first_name | varchar(20) | | | | |
| last_name | varchar(30) | | | | |
| email | varchar(40) | | | | |
| alt_email | varchar(40) | YES | | NULL | |
| addr_l1 | varchar(60) | YES | | NULL | |
| addr_l2 | varchar(60) | YES | | NULL | |
| city | varchar(16) | YES | | NULL | |
| state_id | tinyint(3) unsigned | YES | | NULL | |
| zip | mediumint(5) unsigned | YES | | NULL | |
| last_payment | date | YES | | NULL | |
| next_payment_due | date | YES | | NULL | |
| days_past_due | tinyint(4) | YES | | NULL | |
| login | varchar(16) | | | | |
| balance | double(6,2) | | | 0.00 | |
| phone | varchar(12) | YES | | NULL | |
| registration_date | date | YES | | NULL | |
| cancelled_date | date | YES | | NULL | |
+-------------------+-----------------------+------+-----+---------+----------------+

And list of services like this.
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| service_id | mediumint(8) unsigned | | PRI | NULL | auto_increment |
| service_name | varchar(25) | YES | | NULL | |
| recurring | enum('True','False') | | | True | |
| recurrance | tinyint(3) unsigned | | | 0 | |
| price | decimal(6,2) | YES | | NULL | |
| valid | enum('True','False') | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+


So I should have a two or 3 column list that has something like:
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| reference_id | mediumint(8) unsigned | | PRI | NULL | auto_increment |
| service_id | mediumint(8) unsigned | YES | | NULL | |
| service_quantity | tinyint(2) | | | True | |
| user_id | mediumint(8) unsigned | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+

So that I do something like:
SELECT service_id,service_quantity FROM references WHRE user_id = 'some user id';
(Performing the absolutely most simple query but you get the idea)

which will get the quantity and service_id for the given 'some user id'. Of course, JOIN that with the other two tables to make a bill for hte customer. I actually do have a seperate table called, "invoices" which holds all data for every time a bill is created. That way when tax time comes, if I decide to change prices the actual price a customer paid when he/she paid the bill is still recorded. I may need to create yet another to hold the arbitrary amount of information for each invoice.

Thank you so much for you suggestion and not getting mad at a newb like me :).