CODEWITHSUNDEEP
mysql
Saleh
Saleh

Reputation: 294

mysql tables relationship should it be one to many or one to one

I am designing a database for a website. I have 4 tables for posts. The first table is the master table that should assign a new ID and title for every single post regardless of the post type. The three other tables are cars, houses, and furniture tables. Each one of them should only have the post that is related to it. I am little bit confused of the type of relation between master table and other tables; should it be one to many or one to one. that is, in the cars table, there are columns for price, detail, manufacturer, type, cylinder, fuel, and so on. so, in the master table if the type of post is car, then, this post should be assign a one id in cars table with unique manufacturer. Therefor this is one to one. and each post in the cars table has a correspondent post id in the master table( one to one relationship). while each manufacturer may have many posts in the master table. hence, I assume this is one to many relationship. This is the same case with other tables.

please I do not need any referring to any documentation since I read all mysql official documentation and I know the theoretical prospect of all relationship types but when I turned into the practical part I became little bit confused.

Upvotes: 0

Views: 54

Answers (2)

AlwaysBTryin
AlwaysBTryin

Reputation: 1964

If you want to know if the relationship between X and Y is one-one, many-one, or many-many, ask yourself these two questions:

  1. Can one X have multiple Y's?
  2. Can one Y have multiple X's?

Now count the Yesses:

  • 0: One-one
  • 1: Many-one
  • 2: Many-many

For example, a manufacturer can have multiple cars, but a car only has one manufacturer, so that's many-one. The post/car relationship isn't as clear to me from your description. Can a post have multiple cars? Can a car appear in multiple posts?

Upvotes: 1

mindaJalaj
mindaJalaj

Reputation: 448

You are right on the track, I ll suggest you to store all the post in post table as auto-increment id. Whenever you get any post of any type, make an entry in post table with suitable type. Also make entry about manufacture in post table.

Now take this post id and make a corresponding entry into cars/house/furniture table depending on the type, here in these table add additional details like price etc respectively.

So now post table becomes central repository of all post - depending on many types and manufacturer. You can later add/remove types of post accordingly. making it a scalable.

Further, you thinking right in terms of relationships where you have many to one relation between manufacturer and post(since one manufacturer can post multiple times), and one to one relation between post and its type(since a single post at any point in time will only belong to only one type).

Upvotes: 0

Related Questions