Don't give up lil, we can start a club : The people that people don't want to hire
IT folks: I am looking at mySQL again after a loong break, and I am notoriously bad at figuring out relationships, can I have some advice? I am just messing around with mySQL and C# and what I am doing at the moment is making a system similar to what I used at work, so the idea is this:
For now I want my system to use a machine ID to display info about the machine and it's contact person
s
I have the tables Contact, Machine Ownership and ship to party (which is a stupid name, but what I am used to calling address info after that job
)
my primary keys are a bit wrong at the moment, and that is what I want help fixing.
For person and machine I have ID and SN
For the ownership i currently have company ID and machine SN
--- but then I cannot have for instance a company + machine with several contacts
(as I would end up with two or more instances of ie company ID 1, machine ID 2 to get two different contacts, so it is backwards....
what is the best way to link my info?
Current tables:
| Tables_in_mysap |
+-----------------+
| contact |
| machine |
| ownership |
| shipto_party |
+-----------------+
mysql> explain contact;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(3) | NO | PRI | 0 | |
| firstname | varchar(25) | YES | | NULL | |
| lastname | varchar(25) | YES | | NULL | |
| phone | varchar(15) | YES | | NULL | |
| email | varchar(40) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
mysql> explain machine;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| SN | varchar(10) | NO | PRI | NULL | |
| model | varchar(4) | YES | | NULL | |
| machinetype | varchar(30) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
mysql> explain ownership;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| company_ID | int(3) | NO | PRI | NULL | |
| machine_SN | varchar(10) | NO | PRI | NULL | |
| contract | varchar(4) | YES | | NULL | |
| contact | int(3) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+