Design question

Question

Hi All,

I have a table called Company in our ERP database. We are creating a reporting database for our ERP database and denormalizing the tables for faster querying. So the Company table has both customer records as well as vendor records. I have been thinking to split this table into two tables Customer and Vendor, but was wondering if you guys have any thoughts on the pros and cons.

Thanks for your help.

Answer 1

I moved this post to database design since your question deals with database, and more specifically table, design.  This should provide a more directed response to your question.

---

David Dye My Blog

Answer 2

You will have to test it... If the tables are huge you need to have properly defined indexes and/or perhaps partition the table...

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 3

Best to post DDL for quick assistance.

---

Kalman Toth
Paperback/Kindle:  Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Answer 4

The question here (and truly the same question should be (or should have been) asked: Do you treat customer and vendor as one, or differently, or both? If they are treated as on (and you probably wouldn't have asked this question if they were), then one table is sufficent. If they are always treated seperately, then two tables for sure, since all you end up doing is filtering on one or the other.

If you sometimes treat them the same and sometimes completely different, then use a subclass. For OLTP, you wouldn't duplicate data in the subclass, but for a ETL'd db, you would:

Company (CompanyId [PK], ColumnsSimilarToBoth)

and then

Vendor (CompanyId [PK][FK to Company], ColumnsSpecificToVendor, ColumnsSimilarToBothForReportingSystemButNotERP_OLTP)

Customer (CompanyId [PK][FK to Customer], ColumnsSpecificToCustomer, ColumnsSimilarToBothForReportingSystemButNotERP_OLTP)

Then you have the best of both worlds, and your usage of the subtypes gives you the split that helps performance, mostly by not having to filter on one or the other everytime (also quite useful in the OLTP side of things).

---

Louis

Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.