-1

I'm redesigning our service app, and getting rid of some really awful schema problems while I'm at it. Trying to build the replacement with best practices as much as possible.

I'm having a company table rather than just customer, as it's often useful to identify companies that are not customers (suppliers, contractors, etc etc). I'm trying to decide whether it's better to simply include a boolean field represented in the relevant part of the app by a checkbox that identifies relevant companies as customers (which would become uneditable once the customer has services attached to them), or if I should, instead, have a separate table that's basically just a single field referencing the Company ID that is in turn referenced by any child records.

This similar question asks about records that can be one of several subtypes. While the question is materially different (every policy seems to be only one of the potential subtypes, whereas Companies can be any or all of Customer/Supplier/Contractor etc) its similarity combined with the fact that it has multiple conflicting answers raises the possibility that there is no industry-wide consensus, so:

Is there an established best practice here? I'm not immediately seeing any reasons that other fields should be included in the prospective Customer table, but I'm open to the idea that there might... is that a good enough reason to go with B? Or is this a clear YMMV situation, where both options have benefits, either being equally valid?

Isaac Reefman
  • 537
  • 1
  • 8
  • 26
  • I'm asking whether there's 'an established best practice here' - if it's established, surely that's not a matter of opinion, but of (at the very least) community consensus... – Isaac Reefman Jan 10 '22 at 00:35
  • Is this like haveing a User table with basic info and yet needing "satellite" tables that add additional attributes to a user? – Ross Bush Jan 10 '22 at 00:40
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jan 10 '22 at 00:44
  • @RossBush I don't think so - for an example, in my db a user is a child of 'Person' - because a person could be a user, a customer contact, a salesperson, an account manager, any number of combinations of those, etc etc etc. Those different sub-records all have distinct required data, functions within the program etc. To me this makes it clear that separate child tables are necessary, whereas with the Company/Customer idea here, there are *functional* differences (certain children are valid/invalid) but not so much *data* ones (all companies need the same basic info). – Isaac Reefman Jan 10 '22 at 01:00
  • @philipxy I don't think it does, at least by virtue of the question and content of the answers... the existence of varying answers may suggest that the correct answer to my question is "no, there isn't an established best practice" though... – Isaac Reefman Jan 10 '22 at 01:01
  • You have different types of companies. PS https://meta.stackoverflow.com/q/265928/3404097 https://meta.stackoverflow.com/q/296542/3404097 https://meta.stackexchange.com/q/142353/266284 – philipxy Jan 10 '22 at 01:09
  • @philipxy ooh, thanks for that. I've been away from stackexchange for a while, so even if I *did* know about that I've clearly forgotten. I'll see if I can salvage the question... – Isaac Reefman Jan 10 '22 at 01:19
  • 1
    Thanks commenters and close-voters for your input: I hadn't realised that/why best-practice questions were off-topic for the site. Those who linked related questions helped me find relevant material to help me make my own decision on this too. Time to close the question! – Isaac Reefman Jan 10 '22 at 01:25
  • 1
    I’m voting to close this question because it's off-topic as a best-practice question. While it's looking for a (potential non existent) established norm, the way it must be worded to work will lead either to opinion based answers, or answers that are not very universally useful. – Isaac Reefman Jan 10 '22 at 01:27

1 Answers1

1

I should, instead, have a separate table that's basically just a single field referencing the Company ID that is in turn referenced by any child records.

There are probably several attributes that apply to a customer that don't apply to a non-customer Company, so CompanyID probably won't end up being the only attribute of Customer.

So if that's the case, the clear choice is to have a separate Customer table.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • @IsaacReefman Please ask a new question in a new post & not in a comment. Ask 1 specific question per post. But please research before considering asking a question & reflect research in questions. Basic questions have been asked. [ask] [Help] [meta] [meta.se] – philipxy Jan 10 '22 at 01:54