Turn two database tables into one?

Question

I am having a bit of trouble when modelling a relational database to an inventory managament system. For now, it only has 3 simple tables:

Product
```
ID | Name | Price
```
Receivings
```
ID | Date | Quantity | Product_ID (FK)
```
Sales
```
ID | Date | Quantity | Product_ID (FK)
```

As Receivings and Sales are identical, I was considering a different approach:

Product
```
ID | Name | Price
```

Receivings_Sales (the name doesn't matter)

ID | Date | Quantity | Type | Product_ID (FK)

The column type would identify if it was receiving or sale.

Can anyone help me choose the best option, pointing out the advantages and disadvantages of either approach? The first one seems reasonable because I am thinking in a ORM way.

Thanks!

I would say they are. Why wouldn't they be? I appreciate any comments/suggestions — Larissa Leite, Jul 14 '14 at 18:07
Because they are two different operations that use the same attributes... — Larissa Leite, Jul 14 '14 at 18:10
Thank you. Anyway, my example is much simpler and if possible I would like at least a comment about this specific scenario... — Larissa Leite, Jul 14 '14 at 18:20
[DBA SE](http://dba.stackexchange.com/) is designed for the kind of answer you're looking for. I would advise you to ask it there or request to have it migrated. — Mark C., Jul 14 '14 at 18:22

Edper · Accepted Answer · 2014-07-15T07:16:08.917

Personally I prefer the first option, that is, separate tables for Sales and Receiving.

The two biggest disadvantage in option number 2 or merging two tables into one are:

1) Inflexibility
2) Unnecessary filtering when use

First on inflexibility. If your requirements expanded (or you just simply overlooked it) then you will have to break up your schema or you will end up with unnormalized tables. For example let's say your sales would now include the Sales Clerk/Person that did the sales transaction so obviously it has nothing to do with 'Receiving'. And what if you do Retail or Wholesale sales how would you accommodate that in your merged tables? How about discounts or promos? Now, I am identifying the obvious here. Now, let's go to Receiving. What if we want to tie up our receiving to our Purchase Order? Obviously, purchase order details like P.O. Number, P.O. Date, Supplier Name etc would not be under Sales but obviously related more to Receiving.

Secondly, on unnecessary filtering when use. If you have merged tables and you want only to use the Sales (or Receving) portion of the table then you have to filter out the Receiving portion either by your back-end or your front-end program. Whereas if it a separate table you have just to deal with one table at a time.

Additionally, you mentioned ORM, the first option would best fit to that endeavour because obviously an object or entity for that matter should be distinct from other entity/object.

score 1 · Answer 2 · answered Jul 14 '14 at 18:23

1

If the tables really are and always will be identical (and I have my doubts), then name the unified table something more generic, like "InventoryTransaction", and then use negative numbers for one of the transaction types: probably sales, since that would correctly mark your inventory in terms of keeping track of stock on hand.

answered Jul 14 '14 at 18:23

Joel Coehoorn

399,467
113
570
794

Thank you, Joel! Why would they not be identical? What would be a better way to design a (very) simple inventory management system? – Larissa Leite Jul 14 '14 at 18:27
@LarissaLeite Systems grow and evolve over time. While they may be the same right now, you'll likely find that eventually, you need to keep some different information on receipts than you do on sales. – Joel Coehoorn Jul 14 '14 at 18:40
Thanks, Joel. Seems reasonable. Although, for now, I am trying to keep it simple as it is just an exercise for me trying to integrate Hibernate and Java/JSF. – Larissa Leite Jul 14 '14 at 18:45

score 0 · Answer 3 · answered Jul 15 '14 at 14:59

The fact that headings are the same is irrelevant. Seeking to use a single table because headings are the same is misconceived.

-- person [source] loves person [target]
LOVES(source,target)

-- person [source] hates person [target]
HATES(source,target)

Every base table has a corresponding predicate aka fill-in-the-[named-]blanks statement describing the application situation. A base table holds the rows that make a true statement.

Every query expression combines base table names via JOIN, UNION, SELECT, EXCEPT, WHERE condition, etc and has a corresponding predicate that combines base table predicates via (respectively) AND, OR, EXISTS, AND NOT, AND condition, etc. A query result holds the rows that make a true statement.

Such a set of predicate-satisfying rows is a relation. There is no other reason to put rows in a table.

(The other answers here address, as they must, proposals for and consequences of the predicate that your one table could have. But if you didn't propose the table because of its predicate, why did you propose it at all? The answer is, since not for the predicate, for no good reason.)

Turn two database tables into one?

3 Answers3

Linked