CODEWITHSUNDEEP
mysqldatabase
StudentX
StudentX

Reputation: 2323

Relational Database Design confusion, is there a better way to relate tables then this?

I am trying to create a very basic form of elance like relational database design.

The idea is that there is a seller of service and a buyer of service.

A seller can provide more then 1 service.

A buyer can buy more then 1 service (job).

A job can have more then 1 seller working on it.

A seller can work on more then 1 job.

The following is the design I came up with.

enter image description here

The problem is it seems too cumbersome, for example if a buyer logs in, then we will have to go through all the service table for the services(job) bought by him, then we will have to go through all the seller_job_relationship to get ids of all the sellers working on those jobs then we will have to go through all the seller table to get information about all the sellers working on those jobs.

So is there a better way to link these tables together or is it the way it works ?

This is the first time I am trying my hands on database so am really confused.

Upvotes: 1

Views: 427

Answers (1)

Thies
Thies

Reputation: 696

Your design is fine for a relational database.

Some improvement suggestions:

  • Rename "services_provided_by_seller" to just "service". The providing by a seller is defined via seller_service table. Do not use plural form for names.

  • You may remove "_relationship" suffix, it has no benefit in my opinion.

  • You may simplify the names of the foreign key (FK) attributes: So in "seller_service" call them "seller" and "service" or "provided_service". In the job-table "buyer" is sufficient. Because they are foreign keys (and declaratively marked as such in the DB) it must be an id (an FK always references the PK = primary key).
    Name the FK attributes corresponding to the role the FK plays: E.g., in an order table you may have three FKs all to the same person table: orderer, invoicee, consignee.

  • For the m:n relation tables (seller_services and seller_job) you may remove the surrogate primary key "id" - it is not needed from a relational point of view - and use a compound primary key (id_seller + id_service, because each seller may offer a service only once, I guess).
    But beware, that some persistence frameworks have a bad support for this kind of primary key.

Getting data is simple using SQL and joins. For example, get all services for a buyer (the "?" is the parameter):

select s.service_name
  from service s
  join seller_service ss on (ss.service = s.id)
  join seller_job sj on (sj.seller = ss.seller)
  join job j on (j.id = sj.job)
 where j.buyer = ?
 order by s.service_name

Or just by using a WHERE-clause on a service-entity:

select service_name
  from service
 where id in (
     select ss.service
       from seller_service ss
       join seller_job sj on (sj.seller = ss.seller)
       join job j on (j.id = sj.job)
      where j.buyer = ?)

Upvotes: 2

Related Questions