The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database Design Question

Hi, can someone please comment, I've built it different ways in the past, but now that I think about it, neither of them seem right:

Consider, 2 kinds of users: Buyers and Sellers

Both login through the same same login form, ideally I want a single USER_MASTER table with a USER_TYPE field to differentiate buyers and sellers.

However now consider the THANSACTION_TABLE that should have 2 fields, BUYER_ID and SELLER_ID, that are foreign keys to USER_MASTER.USER_ID

The relationships are:
TRANSACTION_TABLE.BUYER_ID is a FK to USER_MASTER.USER_ID, (Where USER_MASTER.USER_TYPE="BUYER")

TRANSACTION_TABLE.SELLER_ID is a FK to USER_MASTER.USER_ID, (Where USER_MASTER.USER_TYPE="SELLER")

Now my problem with this design:
I dont know how to get the database to enforce the Where part of the two relationships written above.

Does anyone have suggestions?

TIA,

Cheers,
V
Victor Send private email
Thursday, June 23, 2005
 
 
I would probably treat Buyers and Sellers as separate tables. For login you can check against a view that combines the necessary parts of the two.
Lou Send private email
Thursday, June 23, 2005
 
 
So a buyer can never be a seller, and vice versa?
Bruce
Thursday, June 23, 2005
 
 
Perhaps every user goes into the master user table, but two separate tables -- BUYER & SELLER -- are created to simply hold master user IDs as applicable, thus your SQL queries are joining against these tables to enforce relationship  The foreign key for these two tables may then also be the primary key.
Amazinderek
Thursday, June 23, 2005
 
 
"Consider, 2 kinds of users: Buyers and Sellers".

The entities being modeled are users. A user is a buyer or a seller (can, presumably be one or the other or BOTH or NEITHER).

Thus, "buyer" or "seller" is an attribute of the "user" entity.

You should have a master table of USERS. If there is no additional attributes associated with buyer/seller, then the master table will have two boolena columns (one for buyer, one for seller).

If you need to keep additional attributes, you'll probably need two detail tables (buyer or seller). With this approach, you won't need buyer/seller columns in the master table.
somebody else
Thursday, June 23, 2005
 
 
==> I dont know how to get the database to enforce the Where part of the two relationships written above.

You really can't get the database engine to do this for you. You have to hand-code it yourself.

We run into this a lot. So much so, that we've "generisized" it -- it's almost a generic "database pattern" (if there is such a thing) for us as we run into so often.

The way we do it is to custom code the enforcement in triggers. The nice thing w/ SQL server is that we've been able to automate this. We flag the appropriate table(s)/column(s) with a few extended properties, and we have a tool we've generated with CodeSmith that reads the extended properties, and automatically generates the triggers for us, so there's really no extra work, other than setting up the extended properties.

The relational model is not really setup for these kinds of relationships -- you could hand-code the enforcement of your relationships, but you'll find you keep running into it over and over (that's why we've automated it).

FYI -- the academics and "relational purists" out there would scream in outrage at the way we do this, but it works for us.
Sgt.Sausage Send private email
Thursday, June 23, 2005
 
 
What amazingderek said, but there are seperate keys for the Buyer and Seller tables. You can enforce the buyer-transact-seller relationship with those keys.

Imagine if the design was for objects. There would be a User class with Buyer and Seller subclasses. Each Buyer or Seller object has a reference to a User object inside of it.

It's probably best to keep the User table minimal, ie, having to do with authentication, and keep transaction-relavant fields (shipping address, payment address) with the the Buyer and Seller tables. (You'll never need to do a join between a buyer's and seller's hashed passwords, so it's okay if the model makes that a difficult operation.)
slava Send private email
Thursday, June 23, 2005
 
 
I would if possible simply make a main table that holds both the buyers, and sellers.

You then eliminate the need for two fields in the transactions table that relates back to the two main tables. (this results in messay sql)

So, in your transaction table you ONLY have a fk relating back to the main table.

You have a main table. In this main table, you put buyers, and sellers, There is a field that tells you this is buyer or a seller. You are DONE!!

You should have only ONE column in the transaction table that relates back to the main customer/seller table.

If you need to find out if the transaction belongs to a seller, or buyer, then you got to look at the main table. So, you have to traverse back up to the main table to get this information. This same rule apples if you want to find out if the transaction was made by a female, or a male. Or, is the age of the person over 30, or below 30. Would we now start making columns or transaction tables for each of those cases?

So, you can see that ideally, if you *can* put both buyers and sellers into a main table, then you really simply things down the road.

These attributes (fields) like age, sex, and yes buyer/seller ALL belong in the main table, and should be entered only ONCE in the master table. There should not be more then one column in the transaction table to relate back to this.

Having two columns in the transaction table to relate back to the main table (or two transaction tables) --- either way means that you will have to build DIFFERENT sql queries for return sellers and buyers. You should be using the SAME sql to make the join and the ONLY thing you change is the criteria is one of a buyer, seller, male, female…etc…

Of course, the above can’t be implemented in a vacuum, and perhaps if MOST of the software that deals with buyers, and sellers is separate applications, then some breaking out of this into multiple tables likely would have to occur, or has already!.


Albert D. Kallal
Edmonton, Alberta Canada
Kallal@ msn.com
http://www.members.shaw.ca/AlbertKallal
Albert D. Kallal Send private email
Thursday, June 23, 2005
 
 
If I understand what Albert is proposing, I disagree.  (If I don't understand then I don't disagree.)

I think what he's saying is that you have a transaction table with two records for each transaction -- one for the buyer and one for the seller.  If that's the case, we'd have to have another table for the transaction details -- date, amount, status, etc.  Either that or keep the same information on both records in the the single transaction table.  Keeping the same information on two records is a maintenance nightmare ("we fixed the amount on that transaction -- wait -- you mean we had to change it twice?"). 

In the best case under this scenario (two tables), you'll need to join the transaction-user to the transaction to get any useful information.

I think you'll be much happier with one user table, one transaction table, and programmatically keeping sellers from ending up in the buyers column.
Boofus McGoofus Send private email
Thursday, June 23, 2005
 
 
Thinking of natural joins - you can add couple more fields to the UserMaster table:

ID, BuyerID, SellerID, <user data fields>

and reference BuyerID and SellerID in foreign keys. In this case it is enforcible through constraints that only one of BuyerID/SellerID is not null and equal to ID.

This solution is just for the sake of having more options though - it is quite messy, violates normal forms and in this case I'd probably go with 2 tables or triggers - depends on task specifics.
DK
Thursday, June 23, 2005
 
 
Hi All:

I was feeling a bit stupid posting this as I've handled it many times earlier, however the debate here shows that my discomfort was valid.

I've been thinking of another solution which might be more database purist compatible:

Transaction Table will have:
USER_ID1
USER_TYPE1
USER_ID2
USER_TYPE2

There will be 2 FK's:
Transaction Table (USER_ID1, USER_TYPE1) FK to User Master (USER_ID, USER_TYPE)

and

Transaction Table (USER_ID1, USER_TYPE1) FK to User Master (USER_ID, USER_TYPE)

Also we can enforce with a CheckConstraint or a Triger that USER_TYPE1="SELLER" and USER_TYPE2="BUYER"

This will ensure that the database maintains data and relational integrity.

The downside is that it seems more tedious to program.

Any thoughts?

Cheers,
V
Victor Send private email
Friday, June 24, 2005
 
 
I'm going to explain my approach using ORM and its a good example of how good modelling technique gets you the best result.

Object Role Modelling is about finding out the facts about any set of data and the constraints upon that data and uses a fairly simple diagrammatic representation, but it also has a formal language representation using FORML which is a kind of stilted English.

The diagram I created is at http://www.objective2k.com/Gallery/orm1.jpg

That diagram is the same as the FORML representation which is as follows: (the layout may screw up a bit)

Transaction is an entity object type.
    Transaction is an alias for the nested fact type ' User sells to User with TransactionID'
User has Name
    Each User has some Name.
    Each User has at most one Name.
User(userid) is an entity object type.
    Every User is identified by one distinct userid.
    Physical Microsoft Visual FoxPro datatype: Char(10).
Name(name) is an entity object type.
    Every Name is identified by one distinct name.
    Physical Microsoft Visual FoxPro datatype: Char(10).
 Transaction includes a Quantity of Product at a Price
    It is possible that  more than one Transaction includes a more than one Quantity of more than one Product at a more than one Price.
Quantity is a value object type.
    Physical Microsoft Visual FoxPro datatype: Integer.
Product(Productid) is an entity object type.
    Every Product is identified by one distinct Productid.
    Physical Microsoft Visual FoxPro datatype: Char(10).
Price is a value object type.
    Physical Microsoft Visual FoxPro datatype: Currency(10).
Name(name) is an entity object type.
    Every Name is identified by one distinct name.
    Physical Microsoft Visual FoxPro datatype: Char(10).
Product has Name / Name is of Product
    Each Product has some Name.
    Each Product has at most one Name.
TransactionID(TransactionID) is an entity object type.
    Every TransactionID is identified by one distinct TransactionID.
    Physical Microsoft Visual FoxPro datatype: Char(10).
 User sells to User with TransactionID
    For each TransactionID t,
         some User sells to some User with TransactionID t.
    It is possible that  more than one User sells to more than one User with more than one TransactionID.
     no User sells to itself with some TransactionID.
    This fact is nested as 'Transaction'.

This conceptual model is then translated into a logical model, essentially tables and relations and can be represented in an ER diagram.

This is at http://www.objective2k.com/Gallery/erdiag.hpg

Now this is a simplistic representation, I've not shown the rest of the facts about Transactions which would include the Date and so on and which would be facts about Transaction IDs, but I think it should be evident that you have a single table of Users and a table which represents the transaction (whether it includes the detail at that level or the transactionid level is by the by).

There is an additional constraint in the Transaction Object which is a ring constraint, it connects the two Users in an irreflexive constraint which essentially means that a user cannot sell to themself.  How that is enforced in the database depends on the DBMS but most would need to be a trigger on the creation of the record that checks to make sure that both IDs are not the same.
Simon Lucy Send private email
Friday, June 24, 2005
 
 
Simon Lucy Send private email
Friday, June 24, 2005
 
 
That's impressive, Simon. Wow.

The way I read the OP's requirements were that there are two different use cases, logging in and making a transaction. The OP wishes to use a single table to do authentication. However the OP also wants to make the transaction as typed as possible too. These seem to be different desires.

For authentication I'd make one user table with username, password hash and whatever other authentication related fields necessary (like last login timestamp, IP, etc).

For transactions I'd make a table for Buyers and one for Sellers. Use a foreign key in these two tables to refer to the user table's id. The rationale is that Buyers and Sellers are very different types, there's more to each than just a name. There will be fields for Buyers that are meaningless for Sellers (for example, shipping address), and vice versa. It is a waste to have half of your table fields empty (some DBMS's will actually allocate substantial space for these empty fields). Furthermore the operations you want to do will be exclusively either for Buyers or for Sellers but rarely, if ever, for both - all your SQL statements have a TYPE = 'BUYER' or BUYERTYPE = true condition - so why not just put them in different tables to start?

This design would need to be modified if Buyers can be Sellers and Sellers can be Buyers. Depends if this is normal or exceptional. But the OP did say they were "different kinds."

Also I would not use string fields denoting type. TYPE = 'BUYER' is not efficient.
slava Send private email
Friday, June 24, 2005
 
 
The impressive thing is how simple it is to do.

I took Ebay as the kind of site, in that anyone can either be a Seller or Bidder, Bidders go on to become Buyers at some point.

If its any kind of Market application then Buyers can be Sellers in a different transaction.  If there truly are different properties belonging to Users as Buyers rather than Users as Sellers then I'd create a subset of all users and make them Buyers and a subset of all users and make them Sellers.

However, that seems a complication too far and you would end up with the same UserID participating in the Transaction anyway, you would not have a BuyerID and SellerID since that would imply that an individual user can be multiple buyers and/or sellers.
Simon Lucy Send private email
Friday, June 24, 2005
 
 
Simon:
Your modeling is impressive, and I want to keep a single transaction table as well as a single user table.

However the core of the question is how to get the RDBMS (in my case MS SQL Server) to enforce that only buyers go into the buyer_id column and sellers go into the seller_id column.



Slava:
For my purposes, both buyer and sellers have similar data, and its not a waste of space to have them in the same table (I need only 1 extra field for the buyer, its a VARCHAR(40))


Cheers,
V
Victor Send private email
Friday, June 24, 2005
 
 
For MS SQL you'll have to put that constraint into a trigger on creation, although I'd have expected whatever the client is to already validate that.

It's long been my desire to have a DBMS that reflected the actual constraints in the models I create rather than have them disconnected.
Simon Lucy Send private email
Friday, June 24, 2005
 
 
I'm not sure, but I think the key to Simon's model is to realize that Buyer and Seller are pieces of information *derived* from the transaction data rather than *attributes* of a User. Thus, anyone can participate in a transaction as either buyer or seller, eliminating the need to have two separate UserIDs for those wishing to participate as both buyer and seller.

As to restricting what Buyers and Sellers can do, the 'state' of the User can be queried from the transaction table making available whatever is necessary. This assumes of course that anyone has the ability to enter into both types of transactions without regard to their previous transaction history or lack thereof.

Personally, I've always hated the fact that the accounting systems I work with require the creation (and maintenance!) of two separate 'accounts' to deal with entities that both sell to us and buy from us.
Ron Porter
Friday, June 24, 2005
 
 
That's exactly right and with relationships like that (where your Supplier is also your Customer), you end up contra-ing this Invoice against that one with all of the mess that implies.

As always though when you imply relationships solely from transactional data you get into scaling problems and so it often becomes 'simpler' to abstract that fact as a separate entity even if not doing so give you a better mirror to reality.

It so happens that in the Accounting System I'm developing at the moment (well when I don't get pulled into a different direction as I am currently), has precisely that single contact multiple relationships built into it.  You see the Sales or Purchase Ledger (or Accounts Receivable, Accounts Payable) but if a Customer is also a Supplier its just an entry in two tables, the IDs remain the same.
Simon Lucy Send private email
Friday, June 24, 2005
 
 
"It so happens that in the Accounting System I'm developing ..."

Is is safe to assume that you'll let us know when you're ready to go live? We are extremely unlikely to use it here, but my work in a past life means that I know a half-dozen or so small companies that could use alternatives to what they use right now.
Ron Porter Send private email
Friday, June 24, 2005
 
 
Yep, for certain.
Simon Lucy Send private email
Saturday, June 25, 2005
 
 
"I'm not sure, but I think the key to Simon's model is to realize that Buyer and Seller are pieces of information *derived* from the transaction data rather than *attributes* of a User."

Yup.

Users:
Name
UserID

Transactions:
BuyerID (FK to UserID)
SellerID

You know the user is a buyer if there's an entry in the transaction tables in the BuyerID field which is the same as their UserID. Ditto Seller.

This get's rid of all sorts of problems. If you have boolean type fields in the Users table (IsABuyer, IsASeller) what happens if they change their mind and don't buy it? A trigger to get mark the field to false presumable. We avoid triggers if possible, they're a sometimes necessary evil. It get's you out of the problem of nulls. Some people think nulls are OK. They're not.

The structure of the data tables, as reflected by Primary and Foreign keys, should, wherever possible be the thing that enforces business rules.
Database fascist.
Sunday, July 03, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz