question

swarner22 avatar image
swarner22 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
sqldatabasemysql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered
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) );
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

swarner22 avatar image swarner22 commented ·
ahh okay, so I am yet familiar with, since I have not learned. I do know now what to learn, linkable and bridge tables. Bridge tables are for many to many, and linkable are for one-many?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.