Tag Info

New answers tagged

1

There are several instances in which the performance and/or reliability of a system can be improved by loosening or eliminating referential integrity constraints: Data warehouses and ad-hoc reporting databases. These may or may not need to deal with inconsistent data as another answer mentions. However, even if the data is consistent, it is usually much ...


1

I thought I'd make a quick comment on your first question: 1) How do you come up with what entities are needed? The first thing I do for a new project is, either on a white board or big blank piece of paper, write down all the physical and conceptual things about that particular project that I and my team can think of. It's a brainstorm session. ...


1

There are several issues that need to be resolved. Categories The usual use of categories is to identify classes or groups of records. If you create reports, having the values kept in a category table makes it easy for report generators or external data warehouses to properly categorize items. Control Flavors Very often, the values control the operation ...


0

I've seen this used in Microsoft Dynamics CRM. It allows users to customize dropdown lists and the like quickly and easily on the fly. Unless you intend an application to be extensively modifiable by users, I'm not sure if you would want to go down this route, as it could lead to some extra complexity to working with the data from the back end.


0

I actually just had to make a change that linked two of these tables together, which would have had to have been done in code otherwise (and then required a code release whenever this relationship changed). So I suppose one reason is extensibility in saving data about these enums.


0

Business Process Management branch of IT deals with such scenarios every day. You may want to research on various BPMs and see how they could fit into your business. Even if you're not going to invest into any BPM, it is worth researching for any programmer that has to deal with reports, User Entry Forms and Collection of Data from Users. I strongly suggest ...


2

This is going to sound crazy but I'd create a separate service that stored the data in another set of tables, but this other set of tables had no integrity checking. The user would do all the partial work there, and when they were ready you could move their data into the checked schema in a single transaction.


1

Use some sort of identification field for draft mode (IsFinal) on your records. This value can then be taken into consideration when applying any finalization criteria: If IsFinal then //do a bunch of checks You can do this in your code, database constraints or triggers.


3

I see three reasons 1) As jimwise says, they can be changed without recompiling. If they are actually used as enum values in application (do something if it equals that), only usefull thing to change without recommpiling is their name. 2) they CAN contain more than name and id, but two cannot have same id unlike C# style enums 3) they can be used in ...


1

The main benefit I see is that you can add new enum values with a database operation, without compiling and deploying a new version of your application. By treating the set of "Foos" which may exist as data, the impact on the running application of adding new types of "Foo" is kept small.


2

Speed. If you're inserting a whole heap of data, the DB doesn't need to spend its time creating indexes. Having said that don't do this. Create the foreign keys. Never rely on application logic :)


3

The main reason would be for dealing with inconsistent data. Many systems import data from outside and have to deal with failures and exceptions. Imagine you're importing a daily sales log from a retail shop. Inevitably the shop will need to sell something that's not in the inventory, or is mis-quantified (they sell something even though quantity on hand is ...


0

Whether you keep the relation in the database or in code, it's still a relation, and since the database can do it better, you put it there. The database is the central repository for not only the data, but also the schema. Put it anywhere else, and you run the risk of one copy of the frontend program having a different schema than the others. Kaboom.


0

This is a very common design mistake, most people go through this phase :) Why is this schema design bad (if it is)? How can I argue this schema design is bad? It's not taking advantage of a relational database. No type safety, boom. No foreign keys, boom. No referential integrity, boom. Very difficult to query, boom. One massive table to store everything ...


0

Once I see names like property and propertyValue I usually consider it to be reinventing / reimplementing a key-value data store but in sql. I can see the argument that this generic approach seems easier to maintain. However I think you can also make the argument that a little hard coding is code as: Code can be self-documenting Comments can be avoided ...


0

the schema as presented is representing an object model in the database - nothing inherently wrong with that, but it hides the semantic structure of the application... this kind of thing is common in engineering applications, especially those that originated as complex spreadsheets: everything is reduced to a set of variables and categories and values, so ...


0

What is the use case of the data, reporting or just serve app functionality? Relational databases are great for structuring data for reporting, but you can develop more rapidly with NoSQL because if it's lack of schema constraints.


2

First the evident errors: special_part_id in Whole shouldn't exist whole_id in Part should be a FK to Whole, not to user That way the relationships is that Whole is comprised of one or more parts. Now to clarify something: Conceptually you can model a one-to-many relationship that is mandatory both ways, but physically it's imposible to implement. You ...


2

I think that the SRP translates directly to the principles of orthogonal design and full normalization in relational databases. If you recollect that SRP states that there there should never be more than one reason for a class to change then the analogy becomes immediately apparent. However, whether the database should be split into multiple smaller ...


1

The SRP applies to the classes and methods you define. A database is not a user-defined entity, it is a piece of infrastructure that transcends individual requirements and responsibilities (in fact, it is better having to deal with only one database for a system than having to juggle multiple ones). Therefore, there is no point worrying about the fact that ...


5

Item level permissions will require both database modelling and application enforcement. Essentially, You'll want to create an "item security" table that relates the RowIDs and PermissionIDs to each other. The application will then have to determine if the current user has the right permissions to view the item that they requested. The database can also ...


-1

Two concepts from the DB design world apply here. They are "natural keys" and "surrogate keys". A natural key is that combination of columns, the values of which can be used to distinguish the row which contains them from all other rows in the table i.e. that combination of values is unique in the table. Importantly these columns appear "naturally" in the ...


5

First make it clear than Rhyme is also a FK You should do either of the following: Make RhymeId the PK and create an composite unique index with WordID and Rhyme so no combination of two words appears twice. Get rid of RhymeId and make a composite PK with WordID and Rhyme I preffer the second option because you need to keep only one index instead of ...


0

As usual, the answer is "it depends". There's lots of different ways you can do it, each solution will vary depending on your unique situation. I have found that windows tends to barf at any more than 1000 files per directory, so in the past I have created a folder per 1000 files. But you could split it by starting character (eg alphabetically)... or create ...


1

Based on the information you have provided, that seems like a new table (KeyValues table that has three columns: foreign key, key, value where the foreign key links this new table to the existing table. This will allow the following: An arbitrary number of key/value pairs can be stored, which can be indexed and searched and you can join on queries with ...


2

Firstly your question is against the sites guidelines as it is something which will lead to discussion. Still to answer your question, you can create a sqlite db and inside it a table with all this static values to be displayed in drop downs, put the sqlite db in assets folder, on app first launch copy it to applications default db location and read it from ...


3

Model is not normal OtherSpelling should be a separate entity named Spelling DialectApplicable should be a separate entity named Dialect TagReferentCount should be calculated CategoryReferentCount should calculated In WordTag, WordID and TagID should be separate FK besides being CPK In WordCategory, WordID and CategoryID should be separate FK besides being ...


3

is this database design fully normalised? There are many normal forms. This design is mostly 3NF, except for the three ReferenceCount columns. As such it accords with common practice in the software development industry. is it sound? Yes, given your comments to the other answer, I wouldn't be upset if one of my developers suggested this as a ...


3

The most obvious violation of normalization in your DB are the reference counting attributes - they introduce redundant information and could get "out of sync" with the real number of references. "OtherSpelling" might be a violation of normalization, or it might not, this depends indeed on the data you are going to store. I think it is debatable if, since ...


8

is it fully normalised? no. are there problems with it? possibly yes. Just one example: "otherspelling" as a field in the word table breaks both normalisation AND is a problem. What if a word has multiple alternative spellings? And oh, you shouldn't aim for complete normalisation. Complete normalisation is itself a potential problem, especially for ...



Top 50 recent answers are included