asked
How to join multiple tables?
If I was designing a database for a client such as a club, who has members and each member has their very own member code could I make a table that has a column that holds that code number and attach multiple tables two it, each attached table has a different piece of the members codes information, such as a attached table for the emails, an attached table for the first and last names, an attached table for their phone numbers, addresses, etc. so something like this Create table members id(Int auto_increment primary key); Then in this table add a _id column for each of the attached tables like so? name_id email_id phone_id address_id If so what values would I need to give each of these columns in the main table they are all connecting to, as far as key types, and datatypes go? Seth
You're on the right track. Generally speaking, for something like this, I would create an auto-incrementing id column in each table with references from other tables. Unless you expect billions of rows of data, integers are fine for this. Once you have the ids (and set them as primary keys in the tables), you can create foreign keys relating them. Keep in mind that some tables have many-to-many relationships with other tables. For those, you need a bridge table. Here are a few examples: -- main table CREATE TABLE dbo.member ( id int IDENTITY(1,1) , CONSTRAINT member_PK PRIMARY KEY CLUSTERED (id) WITH (DATA_COMPRESSION = PAGE) ); -- general list of addresses CREATE TABLE dbo.address ( id int IDENTITY(1,1) , street varchar(100) , CONSTRAINT address_PK PRIMARY KEY CLUSTERED (id) WITH (DATA_COMPRESSION = PAGE) ); -- bridge table for many-to-many between members and addresses CREATE TABLE dbo.member_address ( member_id int , address_id int , CONSTRAINT member_address_PK PRIMARY KEY CLUSTERED (member_id, address_id) WITH (DATA_COMPRESSION = PAGE) , CONSTRAINT member_address_member_FK FOREIGN KEY (member_id) REFERENCES dbo.member (id) , CONSTRAINT member_address_address_FK FOREIGN KEY (address_id) REFERENCES dbo.address (id) ); -- one-to-many with members CREATE TABLE dbo.transaction_history ( member_id int , id int IDENTITY(1,1) , amount money , CONSTRAINT transaction_history_PK PRIMARY KEY CLUSTERED (member_id, id) WITH (DATA_COMPRESSION = PAGE) , CONSTRAINT transaction_history_member_FK FOREIGN KEY (member_id) REFERENCES dbo.member (id) );
18 People are following this question.
