Source
r/webdev•
coredev

How to store custom attributes efficiently in SPA-project?

So, we've got this system that stores and processes records off persons. Each person has a number of standard attributes like Name, Date of birth and Gender. This looks the same for all our customers.

In order to make the system more versatile, we now need to store attributes alongside each person that can vary depending on the customer. Customer X wants to store shoe size, customer Y wants to store their maiden name. The attributes can be of varying number and type.

How should we organize/store this efficiently? We use a SQL server with an ORM to query our data. And the custom attributes must be queryable through the ORM.

Example 1: Table Customer:

  • Name
  • Gender ...
  • Field1Int
  • Field1String
  • Field1Date
  • Field2Int ...

Example 2: Table Customary to Utes

  • CustomerID
  • AttributeID
  • IntVal
  • StringVal
  • DateVal

I guess alt 1 would be the fastest but feels hacky and is not so easy to extend, but alt 2 is more elegant and extendable, but I expected much slower?

We got max 1.000.000 persons and maby 2-20 custom attributes.

SQL Server with EF on .Net.

Really grateful for your advice!

Posted Jan 17, 2019, 8:44 PM UTC

39
mr_malware•

The approach in example 1 isn't terribly hacky. Sharepoint takes a similar approach.

Example 2 has a typo, but I think you're describing a many-to-many table.

If you need to be able to query through ORM, I would pick the many-to-many in example 2, it will likely be easier to code than example 1's arbitrary columns.

1
pbeocanindesign, front-end development•

So don't you mean one to many? One customer, many attributes per customer?

1
mr_malware•

No, a one to many relationship would have some type of "CustomerId" column on the attributes table. That would imply Customer ownership of each attribute, which I don't think is what OP wants.

Since it sounds like OP has a list of generally used attributes, being shared across multiple customers, you would need an intermediary table, CustomerAttributes, with two columns CustomerId and AttributeId. This allows you to not duplicate attribute for each customer.

1
pbeocanindesign, front-end development•

CustomerID

AttributeID

IntVal

StringVal

DateVal

So like that? I'm pretty sure they tried to emphasize there's no uniform attributes, and that each person would possibly store something different, and I could be mistaken here

1
mr_malware•

I was making my assumption on attributes being shared across customers from this statement:

Each person has a number of standard attributes like Name, Date of birth and Gender. This looks the same for all our customers.

I think it's safe to say that OP needs to provide a little more info in order for us to give them better answers.

1
coredevOP•

Yeah sorry for the typo. Each of our customers would have a pre-configured setup that all persons need to adhere to.

I figured AttributeID would hold the config (mandatory/optional, length, type, etc).

But wouldn't speed be a issue with the second scenario? 1M persons * 20 attributes = a lot of rows.

1
Celessor•

Why should this be queryable through EF? What kind of requirement is there? I would make a varchar field for the JSON object and be done with it properly.

"Give me all records which have custom field X set to Y" can be done with a stored procedure if you really need something like that, and you would import that procedure into your model.

If this option is not good for you, your example 2 would be my pick.

1
coredevOP•

We will need to be able to filter persons by the attributes as well as extract the data for exports.

1