사용자 정의 필드 용 데이터베이스를 설계하는 방법은 무엇입니까?

사용자 정의 필드 용 데이터베이스를 설계하는 방법은 무엇입니까?

---

내 요구 사항은 다음과 같습니다

• 모든 데이터 유형의 사용자 정의 필드를 동적으로 추가 할 수 있어야합니다.
• UDF를 빠르게 쿼리 할 수 ​​있어야합니다.
• 데이터 유형을 기반으로 UDF에서 계산을 수행 할 수 있어야합니다.
• 데이터 유형에 따라 UDF를 정렬 할 수 있어야합니다.

기타 정보:

• 나는 주로 성능을 찾고 있습니다
• UDF 데이터를 첨부 할 수있는 수백만 개의 마스터 레코드가 있습니다.
• 마지막으로 확인했을 때 현재 데이터베이스에 50mil 이상의 UDF 레코드가있었습니다.
• 대부분의 경우 UDF는 수천 개의 마스터 레코드에만 첨부됩니다.
• UDF는 결합되거나 키로 사용되지 않습니다. 쿼리 나 보고서에 사용 된 데이터 일뿐입니다.

옵션 :

1. StringValue1, StringValue2 ... IntValue1, IntValue2 등으로 큰 테이블을 만듭니다. 나는이 아이디어가 싫지만 다른 아이디어보다 더 나은 이유와 이유를 말해 줄 수 있다면 고려할 것입니다.

2. 필요에 따라 새 열을 추가하는 동적 테이블을 작성하십시오. 또한 모든 열을 색인화하지 않으면 성능이 느려질 것이라고 생각하기 때문에이 아이디어가 마음에 들지 않습니다.

3. UDFName, UDFDataType 및 Value를 포함하는 단일 테이블을 작성하십시오. 새 UDF가 추가되면 해당 데이터 만 가져와 지정된 유형으로 구문 분석하는 View를 생성하십시오. 구문 분석 기준을 충족하지 않는 항목은 NULL을 반환합니다.

4. 데이터 유형 당 하나씩 여러 UDF 테이블을 작성하십시오. 따라서 UDFStrings, UDFDates 등에 대한 테이블이 있습니다. 아마도 # 2와 동일하게 수행하고 새 필드가 추가 될 때마다 View를 자동 생성 할 것입니다.

5. XML 데이터 유형? 나는 이것들과 함께 일한 적이 없지만 그들이 언급 한 것을 보았습니다. 그들이 특히 내가 원하는 결과를 줄 것인지 확실하지 않습니다.

6. 다른 것?

---

성능이 주요 관심사 인 경우 UDF 당 테이블 # 6을 사용합니다 (실제로 # 2의 변형 임). 이 답변은 이러한 상황과 설명 된 데이터 배포 및 액세스 패턴에 대한 설명에 맞게 조정되었습니다.

장점 :

1. 일부 UDF에는 전체 데이터 세트의 작은 부분에 대한 값이 있음을 나타내므로 별도의 테이블은 UDF를 지원하는 데 필요한만큼만 테이블이 크기 때문에 최상의 성능을 제공합니다. 관련 지수에 대해서도 마찬가지입니다.

2. 또한 집계 또는 다른 변환을 위해 처리해야하는 데이터의 양을 제한하여 속도를 향상시킵니다. 데이터를 여러 테이블로 분할하면 UDF 데이터에 대한 일부 집계 및 기타 통계 분석을 수행 한 다음 외래 키를 통해 결과를 마스터 테이블에 조인하여 집계되지 않은 속성을 얻을 수 있습니다.

3. 데이터가 실제로 무엇인지 반영하는 테이블 / 열 이름을 사용할 수 있습니다.

4. 데이터 유형을 정의하고 제한 조건, 기본값 등을 사용하여 데이터 도메인을 정의 할 수있는 완전한 제어 권한이 있습니다. 즉석 데이터 형식 변환으로 인한 성능 저하를 과소 평가하지 마십시오. 이러한 제약 조건은 RDBMS 쿼리 최적화 프로그램이보다 효과적인 계획을 개발하는 데 도움이됩니다.

5. 외래 키를 사용해야하는 경우 내장 된 선언적 참조 무결성이 트리거 기반 또는 응용 프로그램 수준 제약 조건 적용으로 인해 거의 성능이 저하되지 않습니다.

단점 :

1. 이것은 많은 테이블을 만들 수 있습니다. 스키마 분리 및 / 또는 명명 규칙을 적용하면이를 완화 할 수 있습니다.

2. UDF 정의 및 관리를 운영하는 데 더 많은 애플리케이션 코드가 필요합니다. 나는 이것이 원래 옵션 1, 3 및 4보다 필요한 코드가 여전히 적을 것으로 기대합니다.

다른 고려 사항 :

1. UDF를 그룹화하는 데 적합한 데이터의 특성에 관한 것이 있으면 권장해야합니다. 이렇게하면 해당 데이터 요소를 단일 테이블로 결합 할 수 있습니다. 예를 들어 색상, 크기 및 비용에 대한 UDF가 있다고 가정합니다. 데이터 경향은이 데이터의 대부분의 인스턴스가

    'red', 'large', 45.03 
   

   오히려

    NULL, 'medium', NULL
   

   이러한 경우, 1 개의 테이블에 3 개의 열을 결합하여 눈에 띄는 속도 패널티가 발생하지 않으며, 값이 NULL이 아니고 3 개의 열에 모두 액세스해야 할 때 2 개의 더 적은 조인이 필요하므로 2 개의 테이블을 더 만들지 않습니다. .

2. 많이 채워지고 자주 사용되는 UDF에서 성능 벽에 도달하면 마스터 테이블에 포함되도록 고려해야합니다.

3. 논리 테이블 디자인을 사용하면 특정 시점으로 이동할 수 있지만 레코드 수가 엄청 나면 RDBMS에서 어떤 테이블 파티셔닝 옵션이 제공되는지 살펴 봐야합니다.

---

나는 한 기록 이 문제에 대해 많은 . 가장 일반적인 해결책은 Entity-Attribute-Value 반 패턴이며 옵션 # 3에 설명 된 것과 유사합니다. 이 디자인을 전염병처럼 피하십시오 .

진정으로 역동적 인 사용자 정의 필드가 필요할 때이 솔루션에 사용하는 것은 XML을 BLOB에 저장하여 언제든지 새 필드를 추가 할 수 있다는 것입니다. 그러나 속도를 높이려면 검색하거나 정렬해야하는 각 필드에 대해 추가 테이블을 작성하십시오 (필드 당 테이블이 아니라 검색 가능한 필드 당 테이블 ). 이를 때때로 거꾸로 된 인덱스 디자인이라고합니다.

이 솔루션에 대한 2009 년 흥미로운 기사를 여기에서 읽을 수 있습니다 : http://backchannel.org/blog/friendfeed-schemaless-mysql

또는 문서 중심의 데이터베이스를 사용하여 문서 당 사용자 정의 필드가있을 것으로 예상됩니다. Solr을 선택합니다 .

---

아마도 다음 구조의 테이블을 만들 것입니다.

• varchar 이름
• varchar 유형
• 십진수
• varchar StringValue
• 날짜 날짜 값

정확한 유형의 과정은 필요에 따라 다릅니다 (물론 사용중인 dbms에 따라 다름). int와 boolean에 NumberValue (10 진수) 필드를 사용할 수도 있습니다. 다른 유형도 필요할 수 있습니다.

값을 소유 한 마스터 레코드에 대한 링크가 필요합니다. 각 마스터 테이블에 대한 사용자 필드 테이블을 작성하고 간단한 외래 키를 추가하는 것이 가장 쉽고 빠릅니다. 이를 통해 사용자 필드별로 쉽고 빠르게 마스터 레코드를 필터링 할 수 있습니다.

어떤 종류의 메타 데이터 정보를 원할 수도 있습니다. 따라서 다음과 같이 끝납니다.

테이블 UdfMetaData

• int id
• varchar 이름
• varchar 유형

테이블 마스터

• int Master_FK
• int MetaData_FK
• 십진수
• varchar StringValue
• 날짜 날짜 값

---

무엇을하든 테이블 구조를 동적으로 변경 하지는 않습니다 . 유지 보수의 악몽입니다. 나는 또한 XML 구조를 사용 하지 않을 것인데 너무 느립니다.

---

이것은 MongoDB 또는 CouchDB와 같은 비 관계형 솔루션으로 더 잘 해결 될 수있는 문제처럼 들립니다.

둘 다 동적 스키마 확장을 허용하면서 원하는 튜플 무결성을 유지할 수 있습니다.

I agree with Bill Karwin, the EAV model is not a performant approach for you. Using name-value pairs in a relational system is not intrinsically bad, but only works well when the name-value pair make a complete tuple of information. When using it forces you to dynamically reconstruct a table at run-time, all kinds of things start to get hard. Querying becomes an exercise in pivot maintenance or forces you to push the tuple reconstruction up into the object layer.

You can't determine whether a null or missing value is a valid entry or lack of entry without embedding schema rules in your object layer.

You lose the ability to efficiently manage your schema. Is a 100-character varchar the right type for the "value" field? 200-characters? Should it be nvarchar instead? It can be a hard trade-off and one that ends with you having to place artificial limits on the dynamic nature of your set. Something like "you can only have x user-defined fields and each can only be y characters long.

With a document-oriented solution, like MongoDB or CouchDB, you maintain all attributes associated with a user within a single tuple. Since joins are not an issue, life is happy, as neither of these two does well with joins, despite the hype. Your users can define as many attributes as they want (or you will allow) at lengths that don't get hard to manage until you reach about 4MB.

If you have data that requires ACID-level integrity, you might consider splitting the solution, with the high-integrity data living in your relational database and the dynamic data living in a non-relational store.

---

Even if you provide for a user adding custom columns, it will not necessarily be the case that querying on those columns will perform well. There are many aspects that go into query design that allow them to perform well, the most important of which is the proper specification on what should be stored in the first place. Thus, fundamentally, is it that you want to allow users to create schema without thought as to specifications and be able to quickly derive information from that schema? If so, then it is unlikley that any such solution will scale well especially if you want to allow the user to do numerical analysis on the data.

Option 1

IMO this approach gives you schema with no knowledge as to what the schema means which is a recipe for disaster and a nightmare for report designers. I.e., you must have the meta data to know what column stores what data. If that metadata gets messed up, it has the potential to hose your data. Plus, it makes it easy to put the wrong data in the wrong column. ("What? String1 contains the name of convents? I thought it was Chalie Sheen's favorite drugs.")

Option 3,4,5

IMO, requirements 2, 3, and 4 eliminate any variation of an EAV. If you need to query, sort or do calculations on this data, then an EAV is Cthulhu's dream and your development team's and DBA's nightmare. EAV's will create a bottleneck in terms of performance and will not give you the data integrity you need to quickly get to the information you want. Queries will quickly turn to crosstab Gordian knots.

Option 2,6

That really leaves one choice: gather specifications and then build out the schema.

If the client wants the best performance on data they wish to store, then they need to go through the process of working with a developer to understand their needs so that it is stored as efficiently as possible. It could still be stored in a table separate from the rest of the tables with code that dynamically builds a form based on the schema of the table. If you have a database that allows for extended properties on columns, you could even use those to help the form builder use nice labels, tooltips etc. so that all that was necessary is to add the schema. Either way, to build and run reports efficiently, the data needs to be stored properly. If the data in question will have lots of nulls, some databases have the ability to store that type of information. For example, SQL Server 2008 has a feature called Sparse Columns specifically for data with lots of nulls.

If this were only a bag of data on which no analysis, filtering, or sorting was to be done, I'd say some variation of an EAV might do the trick. However, given your requirements, the most efficient solution will be to get the proper specifications even if you store these new columns in separate tables and build forms dynamically off those tables.

Sparse Columns

---

4. Create multiple UDF tables, one per data type. So we'd have tables for UDFStrings, UDFDates, etc. Probably would do the same as #2 and auto-generate a View anytime a new field gets added

According to my research multiple tables based on the data type not going to help you in performance. Especially if you have bulk data, like 20K or 25K records with 50+ UDFs. Performance was the worst.

You should go with single table with multiple columns like:

varchar Name
varchar Type
decimal NumberValue
varchar StringValue
date DateValue

---

This is a problematic situation, and none of the solutions appears "right". However option 1 is probably the best both in terms of simplicity and in terms of performance.

This is also the solution used in some commercial enterprise applications.

EDIT

another option that is available now, but didn't exist (or at least wasn't mature) when the question was original asked is to use json fields in the DB.

many relational DBs support now json based fields (that can include a dynamic list of sub fields) and allow querying on them

postgress

mysql

---

I've had experience or 1, 3 and 4 and they all end up either messy, with it not being clear what the data is or really complicated with some sort of soft categorisation to break the data down into dynamic types of record.

I'd be tempted to try XML, you should be able to enforce schemas against the contents of the xml to check data typing etc which will help holding difference sets of UDF data. In newer versions of SQL server you can index on XML fields, which should help out on the performance. (see http://blogs.technet.com/b/josebda/archive/2009/03/23/sql-server-2008-xml-indexing.aspx) for example

---

If you're using SQL Server, don't overlook the sqlvariant type. It's pretty fast and should do your job. Other databases might have something similar.

XML datatypes are not so good for performance reasons. If youre doing calculations on the server then you're constantly having to deserialize these.

Option 1 sounds bad and looks cruddy, but performance-wise can be your best bet. I have created tables with columns named Field00-Field99 before because you just can't beat the performance. You might need to consider your INSERT performance too, in which case this is also the one to go for. You can always create Views on this table if you want it to look neat!

---

SharePoint uses option 1 and has reasonable performance.

---

I've managed this very successfully in the past using none of these options (option 6? :) ).

I create a model for the users to play with (store as xml and expose via a custom modelling tool) and from the model generated tables and views to join the base tables with the user-defined data tables. So each type would have a base table with core data and a user table with user defined fields.

Take a document as an example: typical fields would be name, type, date, author, etc. This would go in the core table. Then users would define their own special document types with their own fields, such as contract_end_date, renewal_clause, blah blah blah. For that user defined document there would be the core document table, the xcontract table, joined on a common primary key (so the xcontracts primary key is also foreign on the primary key of the core table). Then I would generate a view to wrap these two tables. Performance when querying was fast. additional business rules can also be embedded into the views. This worked really well for me.

---

Our database powers a SaaS app (helpdesk software) where users have over 7k "custom fields". We use a combined approach:

1. (EntityID, FieldID, Value) table for searching the data
2. a JSON field in the entities table, that holds all entity values, used for displaying the data. (this way you don't need a million JOIN's to get the values values).

You could further split #1 to have a "table per datatype" like this answer suggests, this way you can even index your UDFs.

P.S. Couple of words to defend the "Entity-Attribute-Value" approach everyone keeps bashing. We have used #1 without #2 for decades and it worked just fine. Sometimes it's a business decision. Do you have time to rewrite your app and redesign the db or you can throw a couple of bucks on cloud-servers, which are really cheap these days? By the way, when we were using #1 approach, our DB was holding millions of entities, accessed by 100s of thousands of users, and a 16GB dual-core db server was doing just fine

---

In the comments I saw you saying that the UDF fields are to dump imported data that is not properly mapped by the user.

Perhaps another option is to track the number of UDF's made by each user and force them to reuse fields by saying they can use 6 (or some other equally random limit) custom fields tops.

When you are faced with a database structuring problem like this it is often best to go back to the basic design of the application (import system in your case) and put a few more restraints on it.

Now what I would do is option 4 (EDIT) with the addition of a link to users:

general_data_table
id
...


udfs_linked_table
id
general_data_id
udf_id


udfs_table
id
name
type
owner_id --> Use this to filter for the current user and limit their UDFs
string_link_id --> link table for string fields
int_link_id
type_link_id

Now make sure to make views to optimize performance and get your indexes right. This level of normalization makes the DB footprint smaller, but your application more complex.

---

I would recommend #4 since this type of system was used in Magento which is a highly accredited e-commerce CMS platform. Use a single table to define your custom fields using fieldId & label columns. Then, have separate tables for each data type and within each of those tables have an index that indexes by fieldId and the data type value columns. Then, in your queries, use something like:

SELECT *
FROM FieldValues_Text
WHERE fieldId IN (
    SELECT fieldId FROM Fields WHERE userId=@userId
)
AND value LIKE '%' + @search + '%'

This will ensure the best possible performance for user-defined types in my opinion.

In my experience, I've worked on several Magento websites that serves millions of users per month, hosts thousands of products with custom product attributes, and the database handles the workload easily, even for reporting.

For reporting, you can use PIVOT to convert your Fields table label values into column names, then pivot your query results from each data type table into those pivoted columns.

참고URL : https://stackoverflow.com/questions/5106335/how-to-design-a-database-for-user-defined-fields