New answers tagged

0

You could start by examining how the roles in your company are structured. The person who asked you to do the program (let's call him John) wants a program that monitors staff progress, so an understanding of the staff hierarchy is essential. What are your entities going to be? How are they going to be related to one another? Ask these questions to yourself ...


1

When you only needed to deal with Transactions, then a single Transactions table was sufficient. Now you need to know about summarisations at the Account level. Yes, you could calculate these dynamically, but this will get progressively slower the more Transactions you have to trawl through. This isn't a User Experience decision. How often will a User ...


6

It depends on what you're willing to do in terms of architectural/performance payoffs. It sounds like at the core you're doing a kind of Event Sourcing, which actually makes a lot of sense when you start talking about domains where it's important to have audit-trails for exactly what happened. I think a key-point for avoiding "code rot" is to make sure that ...


4

I have experienced this problem first hand. It is difficult, and I was only integrating five applications. I can offer a few tips: Decouple the database by using RESTful services for as many things as possible. This allows you to easily create multiple interface versions if needed, and it makes database migrations much easier. If database access for some ...


0

You should either use a NoSQL database and convert your objects to JSON or you could use NCache and simply serialize your C# objects and store them as is. If you need to query against those individual values then you should use its SQL capabilities or tag/group your keys since NCache is more than just a simple .net key value store. Read here store = ...


2

I understand that you have a database single database which you use for all your clients: It's SaaS, so everyone is using the same DB I understand also that the customer data is shared in the database: The only trouble is that "their" customers aren't exclusive to them. They're also customers of their competitors, who happen to also be ...


1

customer 1-m segmentation m-1 client This design allows the customer to be associated with any number of clients and vise versa. However, it means that each client has a different set of segmentation records. That can seem wasteful of space but not critically so. The most significant thing about it is that each client's set of segmentations will vary ...


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 ...


0

Is the evaluation of usage by period the only purpose for which you're storing these data? If yes, then it can make sense to store usages differently for peak and off-peak time. This makes querying the things you describe trivial. If no, it's almost certainly better to store all usage data in the obvious, non-distinguishing way, simply by time and value. ...


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, ...


4

The shared data layer looks a pretty good idea. The solution to minimize the pain is: make test modules that throws exception (like: everything stops working not just a message) when some test fails. Put ALL the test modules in shared folder. I mean all 10+ projects' test modules must be in the same folder (Or a easy to understand path). If a programmer ...


2

The short answer is that you need to put a LOT of time into your shared code to make it as robust/solid/reliable as possible. As you're discovering, you can't afford to have your library code change very often. It may be necessary to split your data layer into two parts - a low level part that does very simple things but never changes (i.e., the shared ...


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.


5

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 ...


5

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 ...


0

An interesting question. Lets leave aside the question of how to represent reposts. Presumably you will have many types of 'post' some of will be sharing some sort of meta data. The main thrust of your problem is whether to store to data of the post multiple times, once per follower, or have a single copy for the poster and dymamically generate 'feeds' ...


1

Erg, this is why I hate EF. Joins are not expensive if you index Non list properties are not always 1-1 relationships, they can also be many to one. Database normalisation is really good if you need to look at your database. (as well as the other reasons for it being good) EF can make and read a 'hard for humans' DB Idealy for a nice readable, flexible ...


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

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

But, if you/the designer knows there are only a few ( <5 ) Categories, and they aren't expected to change, then you could hard-code each Category's title in the View. But if you use the second approach you could also just hardcode the category's fk into the view. Also an important detail in your description is the word "expecting", you aren't ...


1

In a data warehouse setting Database #1 could be the data warehouse of Database #2 (where tables are "flattened out" for performance)


0

Option 2 with constants or enums on the programming end. Although it duplicates knowledge, violating the Single Source Of Truth principle, you can deal with it by using the Fail-fast technique. When your system loads it would check that the enums or const values exist in the database. If not, the system should throw an error and refuse to load. It will ...


1

DATABASE #1 There's not a master table. It means that there's no repository of Categories. Category is a mere description field. Doesn't even matter if we get repeated values (it seems). May be because values in this column are informed by client or customer and it's out of our control. We only want to store it. There's no need (requirement) of ...


1

What's the formal meaning of "more specific search" that you do on sections? If they are conceptually the same they are not more specific. It's a design error to conceptually separate two identical things, usually it means that your analysis didn't capture well the functionality or the purpose of that thing. It's correct to try to achieve the perfect ...


5

I strongly advice against DATABASE #1, for the following reasons : There is no integrity constraint. Invalid values may be created. Changing a description, will require changing all rows with that description. And also all programs that use that value (i.e. you may have an if statement) You can not support multilingua applications I do not see any ...


2

It really depends on what the application calls for. If the application requires a list of categories that might be referenced one or more times (a one to many relationship) then I'd use DB #2. Otherwise, #1 should suffice - and would keep the queries simpler. Its about normalizing the data. You shouldn't just assume that you need to 'normalize' data ...


1

Question 1: In my database implementation I have 2 distinct tables for each of the representations. Would it be more standard or efficient to have just 1 with a type column or is 2 perfectly acceptable despite similar structure? Standard: Well, most DB design shoots for not repeating data (columns) w/in a table nor across related tables. Go for this ...


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

For any parameter for which your application has to interpret the semantics, your option 1 is IMHO the better one since it is simpler, more expressive and the additional flexibility of your option 2 does not bring you a benefit, since when you later introduce new, additional parameters you would need to change your application either. If some of the services ...


2

This sounds like a use-case for event-sourcing. (The following is a bit simplified view) Event Sourcing works by storing "events", instead of data, and then constructing the actual data bij re-running the events all the time. An event log could be like: teacher A created student "Bobby Tables" teacher A assigned student "Bobby Tables" to class Maths ...



Top 50 recent answers are included