data model, many2many with many2one relationship

Question

Hey, I have two types of accounts (customer and provider), I chose the single-table strategy for persistence. Customer creates Orders (one2many) and provider bids on the orders in auction style (many2many relationship, because he can bid on many orders as well as other providers). My question is, is it possible to have these relationships simultaneously ? Because logically it can work. But MDA code generators don't like it. If so, what drawbacks I could come across with this datamodel.

Thanks in advance.

Answer 1

"I chose the single-table strategy for persistence" - that's actually not that good a reason for combining them, in my opinion. Customers and providers are fundamentally different beasts.

The fact that you're having troubles is a clear indication that you're most likely doing it the wrong way - that's true of most things in the IT industry (and probably life itself but you don't need me proselytising on that).

I would separate them out into different tables to resolve this particular problem.

If you really want part of the data to be shared, you could put the common things in yet another table and reference it from the customers and providers tables.

You may want this if a single entity can be both a customer and provider - in that case, you would want the two different table entries to share the same information (such as balance, reputation and so on).

Answer 2

The disadvantage is that you can't enforce referential integrity in the database between the accountID in the accounts table and the accountID in the bids table (which I assume represents the accountID of the provider bidding on the order) because not all accountID values are allowable.

But, don't give up on the single-table solution for accounts, which may well be the correct one for your problem (I can't say for sure not completely understand the relation between providers and customers). Here's what you need to do to both use the single table solution and allow referential integrity:

1. Remove isProvider and isCustomer from Accounts.

2. Add two new tables Providers and Customers. Each table will have an accountID column which is both the primary key in that table and a foreign key back to the original account table.

3. Migrate any additional columns from Accounts that are unique to either Providers or Customers into the appropriate table.

4. Now, the accountID in the Orders table should be a foreign key into Customers, not Accounts. Similarly, the accountID column in Bids becomes a foreign key into Providers rather than Accounts.

Relational integrity and single-table storage for accounts is provided for.