New answers tagged

2

TL;DR - They're designing the database based on how they were taught when they were in school. I could have written this question 10 years ago. It took me some time to understand why my predecessors designed their databases the way they did. You're working with someone that either: Gained most of their database design skills using Excel as a database or ...


5

I think there are at least two parts to your question: 1. Why shouldn't entities of different types be stored in the same table? The most important answers here are code readability and speed. A SELECT name FROM companies WHERE id = ? is just so much more readable than a SELECT companyName FROM masterTable WHERE companyId = ? and you are less likely to ...


0

MyModels - class lib. No dependencies IMyDataRepository - interface. Depends on MyModels MyDataRepository_EfSql - depends on MyModels and IMyDataRepository and Entity framework private EF Context private EF Models private EF Model to Domain Model mapping MyApplication - depends on all three. But the repository is loosely coupled via dependency injection ...


4

Quote of the day: "Theory and practice should be the same... in theory" Denormalized table Your unique hold-it-all table contains redundant data has one advantage: it makes reporting on its lines very simple to code and fast to execute because you don't have to do any joins. But this at a high cost: It holds redundant copies of relations (e.g. ...


12

Why does a good relational schema improve a database? The answer is: it doesn't always improve a database. You should be aware that the what you were likely taught is called Third Normal Form. Other forms are valid in some situations, which is key to answering your question. Your example looks like First Normal Form, if that helps you feel better about ...


10

There a multiple reasons why using one large "god table" is bad. I'll try and illustrate the problems with a made up example database. Let's assume you are trying to model sporting events. We will say you want to model games and the teams playing in those games. A design with multiple tables might look like this (this is very simplistic on purpose so ...


21

I'll be having to implement a database with my boss ... Using dedicated Database Management software might be considerably easier (sorry; couldn't resist). lngStoreID | vrStoreName | lngCompanyID | vrCompanyName | lngProductID | vrProductName If this database only cares about "logging" which product was sold where, when and by whom, then you ...


56

The performance argument is usually the one which is most intuitive. You especially want to point out how it will be difficult to add good indexes in an incorrectly normalized database (note: there are edge-cases where denormalization can in fact improve performance, but when you are both inexperienced with relational databases you will likely not easily see ...


2

This is essentially how many compression algorithms work (if you're interested, here's a detailed explanation of GZIP), which is also why are they so efficient when compressing text in general and XML specifically. If I were you, I would start by asking myself the following questions: Is the size of data actually important? At $0.0300 per GB for an ...


1

The most efficient would probably be a binary format which you could read directly into memory, skipping the parsing step.


2

You can do better than either XML or JSON by exploiting the particular characteristics of your particular data. If the data is completely flat and every row contains the same fields, then CSV is going to be more efficient than either XML or JSON. The reason people generally prefer formats like XML and JSON is because they recognise that the life-time cost of ...


1

You probably won't need the used_emails and used_phone_numbers tables, since you can always obtain that information from your ads table with a simple select distinct query. In this case, using arrays would be convenient, since you can always use unnest to produce separate records. For separate records in phone number, should you end up using records, you ...


4

I shall talk about how Microsoft SQL Server handles this, as that is the DBMS with which I am most familiar. The SQL received by the server is converted into a series of physical operators by the optimiser. The physical operators initialize, collect data, and close. Specifically, the physical operator can answer the following three method calls: Init(): ...


0

You're going to have to cache something somewhere, whether it is using the db to do it with a cursor, or doing it external to the db. However, if you have full control of the db and its schema, you could do some caching of rows and ages of rows within the tables themselves. You could incorporate a timestamp or version number column into your table that ...


1

Stick with one. Although is seems easy enough to just return a bit from the db when you just want to know if the task is repeated, you'll need to manage rules in your application to keep these values synchronized. You've really wasted space and made the database more complicated than necessary. This rears its ugly head in the enterprise when integration, ...


1

If I may make so bold as to tentatively suggest a third solution if you really wanted to limit the number of columns used: A positive value could be used to indicate that an event is valid. A negative value could mean that an event is not currently valid. The benefit of a negative value is that you could see what the repetition period had initially been set ...


1

I concur with @Kilian's answer, and thought I could add something to the discussion: What your describing is how to capture what would naturally be a subclass situation in OOP and map that to the relational model. In OOP, you could have an event (non-recurring), and a subclass recurring event. Or an abstract event with two subclasses of one-time event and ...


0

I think option 2 would suffice since it's clear from your variable name that it's the repititon count and if it's more than 0 then it should recur. I'd use 0 instead of NULL.


4

I would tend to the the "Just the integer column for the repetition interval, and use zero or null to indicate that the event is not recurring" option and use NULL. Semantics of NULL aside, having 2 columns breaks 3rd Normal Form because "repetition interval" depends on the boolean column. And then you an almost guarantee that someone will use the ...


4

Both techniques have their pros and cons. Using a flag and a value means that you use two fields where one would do. Usually this is only a concern if you're extremely short on space, e.g. in embedded systems. It also means that you're storing a value where it's not obvious that it might not apply. Sooner or later someone will look just at the interval and ...


1

Just create a new instance of PDO: $connectionOne = new PDO(" --- dsn --- ", $username, $password, [ PDO::ATTR_PERSISTENT => true ]); $connectionTwo = new PDO(" --- another dsn ---", $anotherUser, $anotherPassword); Just take a look here and here for your driver-specific options.


2

What makes you say it is neglected? The bandwidth actually matters. A most common consequence is that SQL queries are often tailored to return only a small amount of data. For instance, if you need to display the names of customers who were online during the last month, instead of doing a: select * from `Customers` and filtering the results within the ...


5

A grid or datagrid is a kind of user interface widget used to display tabular data. A database is a software system that manages read and write operations on large amounts of structured persistent data. Here are some typical goals/features for a database that don't apply to a datagrid: Ensure transactions always complete in their entirety, or get fully ...


0

I had a similar problem for address data once, our solution was to create a single column containing all searchable data. You will find that indexing all the different columns you want to search on will bloat your DB size by quite a lot. This way reduces that because you only have 1 searchable index, although it duplicates data in a 'messy' search column. ...


1

This can be solved by providing a generic function like GetUsersByWhereCondition(condition) where condition is an arbitrary string to be used as part of an SQL "WHERE" clause like this one: GetUsersByWhereCondition("DOB=#1916-09-10# AND Realname='Foo'") This will allow you to create almost any query condition you like at run time dynamically, even if ...


1

Two options immediately come to mind. A general search which returns any users with a matching attribute: GetUsersByValue(value) GetUsersByValue("1916-09-10") -> bbrown|Bob Brown|1916-09-10", schan|Sally Chan|1916-09-10 Whilst useful, this could be very inefficient, and could generate a lot of noise if you have fields which reasonably contain the ...


0

Practically, ERD are more of database visual aids. You try to model your tables by using relationships depicted by ERDs so you can quickly express yourself esp when discussing with other people. While UML on the other hand is an OOP way of representing the application itself thus deeper and more complex than ERD. One obvious difference, a type of UML ...


0

In my experience, the user column is usually filled in with a "system" account or some other indicator that the update was performed automatically rather than by an explicit user request. I do agree with the others, though, that this particular part of the database design should be refactored. I'd suggest a separate table containing the article ID, a ...


1

There are several ways to handle your audit. Most professional websites consider a user also unregisterd people tracking them with cookies. So your table could be (This is a very simplified example): [User] UserId SessionId Registered Where Registered is a boolean value. Amazon and a lot of big websites tracks your actions when you are not registered ...


2

The user viewing this doesn't modify the document, and so I wouldn't expect you to update this. From your comment above, I would expect the user modifying the document to be authenticated, and that's the user record to amend the document entry with. If it was modified by (say) an automated scheduled task then you may want a SYSTEM user or similar, but ...


1

You actually need to create a Data Access Layer: 1- Create a New Class Library [ProjectName].DataAccess 2- Create a Class Named UserManager. 3- Create a Method called AddUser that takes the User Model as an argument. and inside that method, you write the logic to insert the user to the database. Depending on the Scale of your system, you might want ...


0

UML is a collection of diagrams. There are class diagrams (different at so many level than Crow's Feet notation), Use Case diagrams, deployment diagrams, sequence diagrams, state machine diagrams and so on as you can read in the 790 pages omg's specification. So no, they are a totally different thing.


2

Crow's Feet Notation is one of several Entity Relationship diagramming notations. An entity–relationship model is the result of using a systematic process to describe and define a subject area of business data. It does not define business process; only visualize business data. The data is represented as components (entities) that are linked with each ...


1

You could create the logging functionality in your app and let it just store copies of modified data into another node (may not be right term) on firebase. This prevents you from having some other app running all the time. Then write some sort of other administration app where you can manually run it to pull down all this logging data for analysis. After ...


1

Common solutions to this class of problems: Use HQL. In DAO methods that are for specific purposes, it is easy to use a custom HQL with (left) join fetch to specify exactly what data you want to pre-populate in the model. Manually reference lazily loaded collections. E.g. manually call user.getOrders().size() in some DAO methods. This has the same effect ...


2

If your data source does not provide you with notifications when data is deleted, then I don't see any other choice but to brute-force the solution by polling the source periodically. Depending on the data source, this may violate terms of service if you poll too often because it puts a heavy load on the source server. The ideal solution is to work with ...


1

We had a similar problem with millions of rows per day. I will give an example with just one table and can be done similarly for others. Say the table was orders. orders would have the data for current table. It was always archived on a saturday and the data would be moved to a weekly table e.g orders_20160409 would have orders data for the week of April 9 ...


1

Another approach might be to have a Semesters table. Your core tables that are in use now would take a SemesterID as a foreign key. Put the Active bit on the Semesters table (or query it by datetime comparison, but I'd probably go with the archive bit). From there, you manage only the Semesters archive bit, and either: Inner Join to "active semesters" ...


1

This solution is certainly an acceptable solution, looking at your constraints, and the relatively small size of the database after 10 years. However, the frequency of your archiving shows that the data is inherently time dependent. I'd really put the year and semester or date of validity part of the data. This would avoid the necessity to archive, and ...


1

This would be a nice feature, and maybe there's a market for it if you write your own. However, it is a very complex challenge. For instance, if your target schema would rename a table or a column, the tool would take the new schema with new elements in it, find the existing schema, and compare both. But how could it safely identify the renaming, and ...


2

Most databases use one-way request-response approach: the client contacts the server and (eventually) expects an answer in return. This, essentially, prevents the server from notifying a client about anything; techniques such as polling, exist, but have their own drawbacks (such as the heavy load on server and network). On the other hand, you seem to expect ...


0

It sounds like you need two classes: Category, which stores data about a single category. This is likely to be a simple POJO, typically holding data from a single database row. There may be some behavoural mehthods, but often there are none. CategoryList, which contains a list of categories, or possibly extends somethig like ArrayList. Typically this will ...


1

Am I allowed to do this Yes. You can do whatever you want if it's legal in every other respect. under which conditions? You have to mention original authors (Wikipedia) and use the same license for your derived work, as described here: https://creativecommons.org/licenses/by-sa/3.0/ but in my case I only have small ckunks of information taken ...


-1

Great question - this is something I advocate at the office very regularly. My view is that most of the logic should be in code. It's always very tempting to use a variety of languages because they each have their strengths, but unless you have a perfect development setup (which is very rare), it's preferable to stick to one language. People have ...


1

Previous answers give great reasons to why it's easier/better to put logic in application code vs in a database. One exception I'd like to highlight is when using a big data database/tech stack. In this case, many of the disadvantages go away: You can write unit tests since it's actual code you wrote that sits in the database. You can debug, albeit through ...


8

Two very important points are missing in your pro-database arguments: performance: database code is executed with direct access to the data, thus avoiding unnecessary transfers (be it across fetching API and mapping schemes on the same machine, or across network for client/server communication) consistency: as several applications may access/update the ...


4

Besides all the facts that have been already pointed out, also remember that having business logic in your code rather that the database eventually turns out to be cheaper. When looking for a developer for an application written in PHP and using MySQL as a database, should your business logic be stored in the database, a simple PHP programmer is not enough, ...


16

I am very firmly of the view that when ever possible, business logic should be kept in the software layer and not the database layer. Note, that when ever possible falls far short of always. There are strong arguments for both ways, and as always use engineering good judgement to decide for each project how much weight should be applied to each point before ...


60

See How much business logic should the database implement? for previous discussion. In general, everyone wants things done in the layer they control. Because then they control it. Every database vendor wants people to put as much logic into the database as possible. Because that locks you into the database. The reasoning is that if multiple ...


0

First of all, this is a contrived homework example. In production, I'd probably have to smack a developer really hard if he wrote something like this. However, I assume you've been studying multithreading techniques and in particular, shared resources and locking. Think about how you would implement this in real life - you have 3 people who wish to obtain ...



Top 50 recent answers are included