1

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.

StudentX
  • 2,243
  • 6
  • 35
  • 67
  • This is just the way it works. – Hogan Jul 28 '15 at 17:22
  • What about a ternary relationship between seller, job, and services? Don't have two JOIN tables with two keys each; have one with three keys. http://stackoverflow.com/questions/10597698/decomposing-a-ternary-relationship-into-binary-relationships – duffymo Jul 28 '15 at 17:23
  • 1
    You could just have one table "people" and then have a type code in that table -- if your goal is reduce the number of tables. – Hogan Jul 28 '15 at 17:23
  • @duffymo - as far as I can tell there is no true relationship between job and service needed. Probably need another field in the job table or the sell_job_relationship, or both. – Hogan Jul 28 '15 at 17:25
  • I agree with @Hogan, there should probably be a `job_services_needed` table; it could replace the `seller_job_relationship` table if it contained an optional reference to the seller the service ends up being provided by (assuming only one seller per job_service). It would also allow the buyer to actually define the job before sellers are determined, and provide for a later design expansion point to allow sellers to bid on "open" job services needed by buyers. – Uueerdo Jul 28 '15 at 17:31
  • You won't have to go through the whole tables to find things. You have (or will add) indexes that will allow you to lookup Jobs-by-Buyer or Jobs-by-Seller, etc. – RBarryYoung Jul 28 '15 at 17:40
  • 1
    That is just the way it works, like @RBarryYoung pointed out indexes will help reduce the size of data being queried. http://stackoverflow.com/questions/2955459/what-is-an-index-in-sql - On a side note this relational model really bugs me, try taking a look at this quick doc for some pointers http://www.ntu.edu.sg/home/ehchua/programming/sql/Relational_Database_Design.html – Spade Jul 28 '15 at 17:43

1 Answers1

2

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 = ?)
Thies
  • 686
  • 6
  • 17
  • The plural/singular table name is debatable; I agree on the suffix and some of the naming guidelines you've given; but I've always been of the school of thought that foreign key fields should have either the same name as the field referenced or [table referenced]_[field referenced]. Many databases do not require the field(s) referenced to be the primary key; some do not even require it to be unique. – Uueerdo Jul 28 '15 at 18:35
  • (@Uueerdo:) Often, the FK attribute name simply has the name of the referenced table because this is also the role it plays in the referencing table, so your "school" is right in many cases. But what do you do in the order example (multiple FKs to the same table) ? Or you have a project table and a person table and the project has an FK for the project leader - I would call the FK attribute "leader" then (the role it plays) and not simply "person". – Thies Jul 29 '15 at 08:06
  • (@Uueerdo:) In a relational database (the original authoer asked for) an FK may only go to the PK which is by definition unique. If a DBMS does allow something different, it is not strictly relational. Such a reference is quite untypical (but _sometimes_ there is a use case) so I would document it in an obvious way, and for this special case maybe use a different naming schema, so this exceptional case is recognizable by the first look. – Thies Jul 29 '15 at 08:11
  • @Thies Relationally, a FK references a candidate key. In SQL FOREIGN KEY declares a foreign superkey and references a UNIQUE NOT NULL ie superkey. (Whether declared like that or via PRIMARY KEY.) (CKs are superkeys so a true FK is a special case of an SQL FK.) Calling one CK a PK is just a tradition that has no role in relational theory. Although as just explained SQL lets you define a non-CK as a PK. Of course, relationally a foreign superkey constraint is also perfectly reasonable. – philipxy Jul 29 '15 at 09:20
  • @Thies regarding field naming, I was trying to be brief (at the cost of clarity). Yes, when the relation the key signifies is unclear, I almost always prefix the "base" name I would use, such as something like `leader_person_id`. While I can agree that the use of FK's to non-unique keys is questionable at best, that still does not make them "non-relational", and I've yet to see a good use for them. – Uueerdo Jul 29 '15 at 16:39