2

We have hired a systems design consultant to help use architect a new CRM system, bringing together several business needs into one system, instead of the several we currently have.

It's the typical project that a company spends a fortune on, and then discovers it doesn't do what it thought it would. I'm trying to make sure that doesn't happen to us.

Could I have some community feedback on some parts of the solution our consultant has recommended? Personally I'm unsure, but I'd defer to their expertise, and to this community's collective knowledge.

Our User Tables

Some of our users have verified accounts with us. Some will have bought tickets through a third party provider. Others will simply reach out to our customer service, and may not be customers yet.

One way of doing this would be to use three tables: VerifiedUsers (data from our verified user accounts -- email, name, post code, etc.), TicketPurchasers (data pulled from our ticket provider - also verified -- email, name, post code, etc.), and Individuals (essentially only an email addresses used to contact our customer service).

Obviously it would be great to make connections between these tables, so we can pull together as much information about our customers/users as possible.

This all seems fine to me so far. Where I get a little unsure is that the consultant has suggested the Individuals table should be the master table.

This table is populated with the most "dirty" data: The least verified data sent from people with the lowest at stake (potentially not even customers, sometimes just so they can rant at our customer service team).

Also, if someone has several email addresses they use to reach out to customer service, how are we supposed to make a reliable trustworthy connection between the TicketPurchasers and VerifiedUser tables? It just seems likely that one person could easily have several rows of data in the Individuals table, and we would never really know.

Question: Does this make sense, even just from a database integrity point of view?

Building our own analytics

The consultant has also recommended we start creating our own analytics. This ostensibly makes sense as well: It would be great to be able to collate every touch point our customers/users make.

The idea would be that a table would be filled with a user_id (presumably taken from the Individuals table), an action_id (to reference the action they took -- eg. "Logged into their account", "Contacted customer service") and a timestamp.

This data could be potentially interesting to look at (although I'd like to sit down and ensure it's useful, too), but after a few quick calculations it seems would could fill 1,000,000 rows in a year.

If we continue growing as a company (as we wish to), this table of analytics could easily hit 10,000,000 rows in a few years, and just keep growing.

This scares me.

For a start, I don't like the idea of a table just growing and growing. It makes me uneasy. Secondly, running live operations on a table of millions of rows (which is what would be required), could be prohibitively resource intensive. (Yes, I guess it depends how much my organisation is prepared to spend on this.)

Question: Are my concerns valid, or is it my inexperience?

Edit: In terms of size, it looks like I need to relax: https://stackoverflow.com/a/1995078/199700 Phew.

Thanks for any help!

Improve this question
9
  • I'm not sure there's enough information here to really answer your question - data validation and data cleansing are not really much to do with schema choice, unless you want to change your application to deal with faulty logic potentially? Could you post the schemas themselves that are being suggested? In addition, 10 million rows is not a lot - have you considered an archival system to stop the table growing?
    – George.Palacios
    Commented Feb 5, 2019 at 11:43
  • @George.Palacios Hi, thanks. My question is not about data validation or data cleansing - there is little we can do about that (we're not going to ask for more details from users before they can contact our customer service). So it becomes about schema design, having this particular limitation in mind: Is it wise? Secondly, archiving data would prevent us from performing live operations on it, which would negate the point of collecting it. Good to know that 10m rows isn't a lot. Thanks
    – Chuck Le Butt
    Commented Feb 5, 2019 at 11:47
  • @George.Palacios I've added some basic outlines
    – Chuck Le Butt
    Commented Feb 5, 2019 at 11:53
  • 2
    I don't like the sounds of 3 separate tables for user data. A single user table with flags for the various ways they may have contacted you and whether or not the data has been verified would, IMO, make things a lot cleaner and easier to manage and maintain. I don't really understand what you would gain by having separate tables.
    – Dave
    Commented Feb 5, 2019 at 13:22
  • 1
    Are they suggesting the email address as the primary key across all records (or at least an external key?). Because if so, I could see why they would want the individuals table as the master "customer" table. A similar system I have seen uses a "Leads" table, which (via workflow) users review and convert to actual "Customer" records once they are validated or have purchased something. It's hard to make that call without knowing your business though. The consultant doesn't seem like they are doing anything "wrong" yet, if that helps.
    – Jacob H
    Commented Feb 5, 2019 at 13:48

1 Answer 1

Reset to default
1

If we continue growing as a company (as we wish to), this table of analytics could easily hit 10,000,000 rows in a few years, and just keep growing.

This scares me.

10M rows isn't a matter to be worried about. Even 10B rows isn't a matter for a single host. The main criteria is the throughput of the DB/engine. To prevent hangups and outages you need to be able to ingest/return the data at the rate at least 3x faster than your actual (and potential) average load. 10x is better of course. This goal can be achieved in two ways. First is the DB/queries optimization. In most cases results are more than acceptable. But every engine has its limitation that can be overcomed by some kind of scaling. Another way is the clustering, segmentation, load balancing etc. Here the DBA should pass the problem to the system architect. This is a different set of competencies. Here is the good explanation how it works:
https://github.com/donnemartin/system-design-primer

Improve this answer
1
  • Yes, this is good to know. Phew
    – Chuck Le Butt
    Commented Feb 5, 2019 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.