0

I am seeking some advice on better database design of common fields of business accounts.

At least moment our business accounts are separated into two types: suppliers and retailers.

So I am designing the tables as 

table: company
- id
- company_name
- company_address
- some other common fields

table: supplier
- id
- company_id
- additional custom fields for supplier

table: retailer
- id
- company_id
- additional custom fields for retailers

is this a good design? or I should not have a common company table but distribute those common fields into supplier and retailer?

onegun
  • 803
  • 1
  • 10
  • 27
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Oct 16 '18 at 09:57
  • @philipxy, i believe the picked answer in the question mentioned by you actually is the approach adopted by me, thank you – onegun Oct 16 '18 at 10:10

2 Answers2

0

Although your solution needs the least disk space to be saved, I would actually prefer a single table that has all 3 types of fields:common, supplier, and retailer. Here are the reasons:

  1. You will have to write less joins
  2. Despite (1), that design does not break normal forms. Indeed, in the future, you might have a company which will be both supplier and retailer
  3. If you implement 3 tables, your joins will sooner or later meet the problem of polymorphic associations (search around for this)
George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • However, there are quite a number of different fields for both supplier and retailers. so that may cause some problem in we combine into one table – onegun Oct 16 '18 at 09:52
  • They all exist in it. The downside would be a large number of null values, I guess. And the inability to make them non-null, of course. – George Menoutis Oct 16 '18 at 09:54
0

I agree with your suggestion, and you should probably just have a single company table. You may add a column with what type the company is (e.g. supplier or retailer):

table: company
- id
- company_name
- company_address
- company_type
- some other common fields

The company_type field itself might be an ID into another table which stores the actual name for the type, and maybe some other metadata.

If the nature of suppliers and retailers are such that they need wildly different columns of information, then one possibility would be to have a company_metadata table:

table: company_metadata
- company_id
- key
- value

Here is an example of what this table might look like:

company_id | key             | value
1          | supplier_origin | Brazil
1          | wholesale       | true
2          | retailer_owner  | false

Using a pivot query, you may try to find any key for a given company which you want.

SELECT MAX(CASE WHEN key = 'supplier_origin' THEN value END) AS supplier_origin
FROM company_metadata
GROUP BY company_id;

If you can't find it, it is not there. This key value approach solves the potential problem of just adding more columns to the company table not scaling well.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • since this will also be a join, will it also cause polymorphic associations – onegun Oct 16 '18 at 09:53
  • This is very interesting. A key/fieldname/value table? It's like...a table split into 6NF for each field and then unioned together? Spicy! – George Menoutis Oct 16 '18 at 09:53
  • Response to both comments: Yes, you might need a join, but @George ideally the OP would not need this. It would be a last resort if the two types of companies really had different behaviors. The thing is, we can't just keep adding disjoint columns to the `company` table, especially as the number of types keeps increasing. – Tim Biegeleisen Oct 16 '18 at 09:54
  • @Tim Well, I admit I haven't seen this before. In the end, a question about design cannot be answered without use statistics, right? So I figure your solution is stronger if the supplier/retailer-only fields needed in the average query are few. onegun: Tim's solution is not a polymorphic association. The join condition is not dependent on anything. – George Menoutis Oct 16 '18 at 10:00
  • @GeorgeMenoutis comany_metadata is EAV not 6NF. EAV is metadata plus data for/encoding straightforward tables. 6NF is straightforward tables that are projections that join back to straightforward tables. Here the original table is a left join of a supertype & subtype tables. 6NFs would be projections of those tables that join to them & left join to the original. – philipxy Oct 16 '18 at 10:53
  • @philipxy thanks for this info – George Menoutis Oct 16 '18 at 10:57