6,572 reputation
1923
bio website mooseware.ca
location Mississauga, Canada
age 52
visits member for 3 years, 1 month
seen 13 hours ago

I'm a professional software developer with more than twenty years of experience across many industries and the entire systems development lifecycle. I'm the principal consultant at Mooseware Limited.

@joelabrown

google

facebook

linkedin


Mar
31
comment How do I design a database for a resource scheduling and allocation application?
@vzhernovoi - I've edited my answer to show what the ALLOCATION table would look like if you wanted to enforce the "one project at a time" rule through declarative constraints. If you have multiple resource types then you should probably have one allocation table per resource type table. There are other ways around this but it would involve making design decisions that could have unintended negative consequences. Google around for the term "entity-subtyping" for a discussion of how to track multiple types of an item in a relational database.
Mar
31
revised How do I design a database for a resource scheduling and allocation application?
Expanded answer to reflect follow-up question from OP in comments.
Mar
31
answered How do I design a database for a resource scheduling and allocation application?
Mar
20
comment In what data type should I store an email address in database?
To look at it from another perspective, perhaps you might want to make a business decision to say that you just don't want to do business with anyone whose email address is longer than 80 characters, because they are probably nuts.
Mar
19
answered Best relational database structure for this data
Mar
18
comment Multiligual database design
@Thomas - There could be an important difference between things you want to publish, e.g. product information, and things that your users (or third parties) supply to your systems, like messages. You can control what you publish but you can't force users to pick a language, or even to use just one, even in a single message. Unless you plan on doing a machine translation of user messages they will only ever be in the language the user chooses anyway. For user-supplied data you may be better off just ignoring the language. It will be what it will be.
Mar
18
comment Multiligual database design
@ChrisTravers - I thought about that too, but I went with the assumption that URL has a language element. Still, if that were easily predictable (like a specific query string component) then for sure it would be better to normalize it out.
Mar
17
answered Multiligual database design
Mar
16
answered PRIMARY KEY CONSTRAINT fundamentals
Mar
16
revised Tape rotation strategies
Retagged to get more of the right kind of eyes on the question.
Mar
16
comment Database design that handles data growth with time
+1 for sargable expressions!
Mar
12
comment Extensible Asset Database Schema
@phobos51594 - (cont...) The important thing to keep in mind is that data models are just that: models. That means they are an approximation of reality with compromises made based on (hopefully well considered) assumptions. If your assumptions are that things will always go out once and come back once then option #3 is simple enough. If you want to be ready for when the actual activity is more complicated then I think option #2 is more future-proof.
Mar
12
comment Extensible Asset Database Schema
@phobos51594 - You aren't solving a normalization issue with the design in your sqlfiddle. Carving off sub-type columns into separate tables makes things a little worse, since now you could have multiple records for checkout_info and checkin_info. I wouldn't think you'd want that, but you know your requirements better than I do. The crux of the problem is whether you consider a check out and a check in one thing (as Leigh does) or two or more things - as I would. If you consider it a pair of things that always goes together, you just have to decide what to do when one day it doesn't.
Mar
12
comment Extensible Asset Database Schema
@LeighRiffel -If you have a type column in OP's option #2, then there would be a code for check-in and a code for check-out and you could easily add another code for inventory-confirmed. One of the reasons OP's option #2 is better than #3 is that checking in and out is very likely to going to include more information than just a date/time. There could be columns for who did it, where they did it, what terminal they did it from, what notes there were re item condition, etc. This gets to look a lot like 0NF when there is more than a date/time to be tracked with each step in the process.
Mar
11
answered Extensible Asset Database Schema
Mar
8
comment Relational database for address model
If you really need truly international coverage (and if you are sure you need to compartmentalize address fields) then you might consider one of the open standards, like OASIS Customer Information Quality v2.0 (oasis-open.org/committees/ciq/download.html).
Feb
28
answered Best practices for history/temporal tables?
Feb
21
comment ERD Review Please
I don't know what you did to draw your ERD, but your Crow's Foot Noation (en.wikipedia.org/wiki/Crow's_foot_notation#Crow.27s_Foot) is backwards.
Feb
19
comment Same product name with different price in different states
@KumarHtabmas - Yes, looks good.
Feb
19
comment How to normalize data based only on the repetitions
@just.another.programmer: See the edit to my answer. The SQL you're proposing almost right. Use not equal to one since you want to have some relationship, but not multiple relationships.