Database design when data is unknown about an object?

Question

I am wondering if there will be consequences of the following DB schema. Say I'm writing an object. I am not sure which place properties will be stored in the database. I am thinking of creating two tables: one for storing the necessary (or general) information, and the other for storing additional information.

Table 1 - Location

• PK PlaceId
• Name
• Lat
• DLN
• etc. (all common fields)

Table 2 - PlaceData strong>

• PK DataId
• PK field name
• PK FK PlaceId
• FieldData li>

Use case

I want some visitors to be able to enter custom fields about the place. For example, a restaurant is a place that can have the following fields: HasParking, HasDriveThru, RequiresReservation, etc., But a car dealer is also a place, and these fields do not make sense for a car dealer.

I want to support any place from one table (well, the 2nd table has custom fields), because I don’t know the number of types of places that will ultimately be added to my site.

common goal

On my asp.net MVC site (C # / Razor), where I show place , it will display the attributes as an unordered list populated: SELECT * FROM PlaceData WHERE PlaceId = @0 .

Thus, I will not need to specify empty field names in the view (or do a string.IsNullOrWhitespace() check for each field). Which I would have to do if each attribute was a table column.

I assume this scenario is fairly common, but are there any better ways to do this? In particular, in terms of efficiency? What are the main disadvantages of this scheme?

Answer 1

If you want your application to be able to create its own custom fields, this is a great model. Mantis Bugtracker also uses this to allow admins to add custom fields to their tickets.

If in any case it is a programmer who is going to create a field, I have to agree with pst that this is a premature optimization.

Answer 2

Your idea is called an entity-attribute-value table and, as a rule, bad news in a DBMS. RDBMSs focus on highly structured data.

Common parameters:

• Model db further in the DBMS, which is most likely if someone is holding back specs from you.

• Stick to RDBMS using XML columns for data whose structure is variable. This makes the most sense if a relatively small part of your data storage scheme is semi- or unstructured. Speaking from the point of view of MS SQL Server, this data can be indexed, and you can perform checks that your data matches the definition of the XML schema.

• Browse to a non-relational database like MongoDB, Cassandra, CouchDB, etc. This is what a lot of social sites are, and I suspect blog sites are working. It’s also wise to use a combination of RDBMS and non-relational storage if your needs require it.

EAV becomes useless because you create a database in the database and lose all the benefits provided by RDBMS (foreign keys, forced data execution, etc.) and the SQL code needed to restore your objects. from lasagna to fettuccine to spaghetti in no time.

Given the information added to the question, it would be nice to create an XML PlaceDetails column in the Place table. You can also split this column into another table with a 1: 1 ratio if this requires performance requirements.

The surface for this is that you can retrieve data using very simple SQL code, even using xml data type methods to find data. But this approach also allows you to do more sophisticated presentation-oriented data parsing in C #, which is better suited for this purpose than T-SQL.

Answer 3

At any time, you can add new columns to the database (always observing the third rationing rule ), so you have to go with what you want and only create a second table if necessary, or if such columns break any of the usual forms .