Not storing the end date and using the start date of a different record/row make each individual row hard to reason about. Querying gets harder.
SQL Server 2012 introduced
LAG to help with these kinds of queries. Full explanation here.
A short example for your case:
LEAD(StartDate) OVER (ORDER BY StartDate) as EndDate
This makes it possible for you to store only the start date in the database table, but then create a view which returns a table that contains both the (stored) startdate and (calculated) end date.
This gives you the best of both worlds. Internally, you ensure data integrity. Externally, you have a view that's easier to process/query further.
Overall, your assesment is correct. Storing the end/start date twice is easier (at the cost of data integrity), whereas removing the duplicate values is gives you better data integrity (but takes more effort to implement.
This type of problem is often a "pick your poison" type of deal. For example, consider the example of storing the folder/file sizes in a directory system. This makes the problem outset a bit more blatant, due to the recursive nature of directory structures.
- Do you store a size for every folder separately? Then you only have to query the folder to find its size, but it's possible that you store the data in a way that a parent directory's size does not equal the total size of its content (= fast querying; but open to data inconsistency)
- Do you calculate a top-level directory's size based on its contents? Then you're guaranteed to get the right answer, but you have to recursively calculate the size of the folder's contents every time you want to know a folder's size (= guaranteed data consistency; but slow querying)
But that's a choice you're going to have to make. What are your priorities? Speed of development? Or quality/consistency? There is no one true answer.