Very Noob MySQL Question

[CrazYmonkeY159]

I am a retard!



Um ok so i have a question.

How can I make a database structured in such a way that I have a primary key which can be mapped to multiple values

Imagine you have an ID number which can be mapped to an account number (some accont numbers can have n ID numbers map to them but not the other way arround, like a shared account where two or more people have access to it)

say if your ID was 1
then ID1 can map to acc #1, acc #2 , .. , acc #n

please forgive my coherence, i havnt slept for a looong time *__*

 

[stricken]

its called relation. as in "relational database".. and the relation is created in your query....

so e.g. SELECT * FROM table_with_account_data WHERE table_with_account_data.customer_id = 1;

otherwise... back to mysql school for you young paddiwan.

 

[ToxicBunny]

Ok.. if the data is in the same table, you can't have the primary key more than once.....

BUT if your data is in disparate tables then you can map multiple ID's to multiple accounts... you could use a mapping table as an example

 

[ufis]

TABLE_ID
id_no (primary key)
more stuff here

TABLE_ACCOUNT
account_no (primary key)
more stuff here

TABLE_ID_ACCOUNT (edit: this would be the mapping table mentioned in previous post)
id_no (foreign key to TABLE_ID.id_no)
account_no (foreign key to TABLE_ACCOUNT.account_no)

put a unique constraint across both columns (not each separately) - many to many
unique on id_no - 1 account to many id's
unique on account_no - 1 id to many accounts

This about what you want?

 

[hof]

You're looking at a one-to-many relationship.

There's no need for a junction table, as in uFiS's example above.

CREATE TABLE IF NOT EXISTS `user` (
`user_id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `account` (
`account_id` int(10) NOT NULL AUTO_INCREMENT,
`user_id` int(10) NOT NULL,
`balance` float NOT NULL,
PRIMARY KEY (`account_id`),
FOREIGN KEY (`user_id`) REFERENCES user(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 

[ufis]

You're looking at a one-to-many relationship.

There's no need for a junction table, as in uFiS's example above.

With unclear "specs" I was trying to cover as much ground as possible Doing 1..* both ways as well as *..*