Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2485
  • Last Modified:

Two Entity or 1 Entity to define ,... Buyer and Seller!

Hi Experts

ER Diagram for a Auction site, example eBay.
1 Account has 1 Profile (1.1 relationship).


1 account can belong to 1 Buyer OR 1 account can belong to 1 Seller
also a Buyer can become a seller and a seller can become a buyer!
 
-----------------------------------------------------------------------------------------------------------------
The question: In this case do we have........
-----------------------------------------------------------------------------------------------------------------
Option#1: seperate table for "buyer" and "Seller" and refernece the Account table.
i.e

Buyer
-----
(((no PK?))
(FK)AccountID
Field1
Fieldn

Seller
------
(((no PK?))
(FK)AccountID
Field1
Fieldn


OR
Option #2: 1 "Account" table/AccountID for both Buyer and Seller?
-----------------------------------------------------------------------------------------------------------------
In eithercase either option will work OK! but which option would you recommend? for best practise, and why?
0
Khou
Asked:
Khou
  • 7
  • 5
  • 2
2 Solutions
 
SharathData EngineerCommented:
1st options is good with a unique buyer id and seller id in respective tables. Account ID may be duplicated in these tables but not buyer/seller id.
0
 
KhouAuthor Commented:
See Attachment: I am thinking of going with Option #1.


Note: Buyer/Seller table, FK AccountID and FK ProfileID are not unique! Reason -- seller can be buyer, buyer can be seller.





ERD.jpg
0
 
SharathData EngineerCommented:
So 1st option is pretty straight forward.
In 2nd option, you can have one table 'User'. Add another column to this table, say UserStatus.
If UserStatus = 1 then Buyer
                      = 2 then Seller
                     = 3 both buyer and seller.
In this way also you can implement. Here you will have only one table.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
KhouAuthor Commented:
I think it would be better if it was norm even further!
Option #3


Account
PK AccountID
...
...

Profile
PK ProfileID
...
...

AccountProfile
PK AccountProfile
FK AccoundID
FK ProfileID
...
...

Seller
PK Seller
FK AccountProfile

Buyer
PK Buyer
FK AccountProfile


(seller/buyer/bid/listing relationships same as option #1).
0
 
Mark WillsTopic AdvisorCommented:
Yes - I was going to suggest a third option - until I saw yours - so job done.

the "unique" person (or company) is the account - they can take on different roles as either seller or buyer or both - in which case they become subsidiaries of Account and have (potentially) their own unique persona's

the second best is option 1 where they are seperate but could be related via an account id - trouble is common information (like name and address) is replicated. So, definitely best to go option 3.

0
 
Mark WillsTopic AdvisorCommented:
The only "gothca" is that you must know what role an account was acting under to determine if they were buying or selling... So, could be implied by type of transaction, or, by explictly using their (globally unique) buyer or seller profile.
0
 
Mark WillsTopic AdvisorCommented:
s/b "gotcha" as in "got caught out" - my typing is really getting very ordinary...
0
 
KhouAuthor Commented:
Here's another scenareo where it can be used (See attachment)

-User can be buyer
-User can be Seller
-Seller has 1 SellerAddressbook of previous Buyers
-Buyer can be added to many Selleraddressbook
-Seller can send an invoice to 1 "newbuyer" OR Seller can select 1 previous buyer from his Selleraddressbook.

are the (tables? cardinality? relationship?) ok here?



Invoices.jpg
0
 
KhouAuthor Commented:
Correction: This should be fine now?

Invoices.jpg
0
 
Mark WillsTopic AdvisorCommented:
It almost sounds if you are thinking of scenarios to support your design.

While design is very important, I am afraid I am not the purist that maybe I once was. The reality after many exceptional designs (well someone has to recognise it) that they do not always get used that way, nor do they ever reach ultimate fullfilment unless of course there is the business requirement in the first instance.

And there in lies the catch 22 of design - what is good and solid design that achieves a business goal versus a great design where many goals could be achieved, and then still be flexible enough to adapt to tomorrows emerging requirement - heavily reliant upon the crystal ball, more so than the ultimate design (well, that is my experience). And I am talking about commercial packages here where they must accommodate a wide range of customers.

So, back to your designs... They are good, and you have found that it is flexible and adaptable to a few different and fairly coold features. But first and foremost does it provide a facility for mere mortal users to get the information they need to get their jobs done - and that design can mean the difference between functionality providing data or usability providing information.

0
 
Mark WillsTopic AdvisorCommented:
Three things on the design -

first, I would have thought that newbuyer was an instance of user.
second, invoice total should really be calculated dynamically.
third, the address book - is really just a recognition of prior business - the buyer info (much like newbuyer) should be part of the buyer profile... in which case is not needed in the invoice.

But if you really have such large data volumes where the runtime calculation of totals is impossible, then I guess it "works" - just how is it being maintained ?
0
 
Mark WillsTopic AdvisorCommented:
Maybe the address book and the invoice total could serve a unique dual role whereby it does provide a summary of joined interactions between buyers and sellers - just a thought...
0
 
KhouAuthor Commented:
Hi mark_wills

Thanks for your comment

Before answering your questions I would like to mention that some diagram represented on this page has been taken from a small section of a larger system.
 
#1 "first, I would have thought that newbuyer was an instance of user."
Without seeing the rest of the design, this would make sense YES, and I would agree with you in this case; However when considering the overall system design, it was not feasible to do this.
 
A feasible solution found was to merge "Newbuyer" with AddressBook (see attachment). This now means a newbuyer can be stored in the addressbook, even where the buyer has not registered at the time the invoice is sent to him/her.

Now what happens if the user decides to register an account? When the addressbook page is open the software would check to see if the newbuyer "emailaddress" has been attached to a registered user account then update/synchronize the Addressbook table according to the users registered data (i.e make proper reference to registered buyer and delete newbuyer record from the addressbook.)

Im aware that this is not the BEST design, but its the best fessible solution in this case, what would be a better design is to do it the way you mention above.

#2 "second, invoice total should really be calculated dynamically. "
Did not like storing the computed values? :)

#3 third, the address book - is really just a recognition of prior business - the buyer info (much like newbuyer) should be part of the buyer profile... in which case is not needed in the invoice.
Agree, -- corrected in #1.


Note:
"Transaction" table is a link table to  "Account", It could be removed, if really wanted to (its not important but good to have).

Name change:
Seller = Provider,
Buyer = Customer,


p.s The design could be improved further but I stop unless you want to say something? :-)





Transaction.jpg
0
 
Mark WillsTopic AdvisorCommented:
That is looking good, and wasn't particularly aware that it is a component of a larger design other than the design was centered around the invoice transaction, so did expect more around it... It is refreshing to see you use comments in the last line of item 1 above - it probably is the litmus test for "reality", and good to see !!

#2 - nah, can understand why, but don't like it, and normally don't recommend it, but you do what is needed for you...

Design looks good. Think it will work well for you - but given on the basis that we have not discussed the business, just looking with techo eyes...

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now