5,784 reputation
1721
bio website mooseware.ca
location Mississauga, Canada
age 51
visits member for 2 years, 7 months
seen 2 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


Oct
25
comment Are 2+ Foreign Keys a Bad Idea in any Association / Junction table?
@zdxai - As long as your tables have a PK, it won't matter for the exercise whether the PKs are natural or surrogate (i.e. a string or a sequential number). You can make the names the PKs of their respective tables if you like, or you can make the keys integers to avoid the issue that David Cummins pointed out in his original answer.
Oct
25
comment Are 2+ Foreign Keys a Bad Idea in any Association / Junction table?
@zdxai - Draw your tables defined the way I suggested, and show me a sample population where you can have one example of a Prius being a Toyota and another example of a Prius being a Chrysler. Normal Forms can't stop your database from being populated with data that is wrong, but it can stop your database from being populated with data that is inconsistent. If you don't see the benefit of the normal forms then you haven't studied them hard enough. They are the #1 best practice in database design.
Oct
25
answered Are 2+ Foreign Keys a Bad Idea in any Association / Junction table?
Oct
21
comment Advantages of middle tables in one-to-many relationships
@SephVelut - Avoiding nulls is only important if you have a performance or space problem and your foreign key is populated only a small fraction of the time. A lot of people like to avoid sparse table, but extending that notion to an optional column is overkill.
Oct
20
answered Advantages of middle tables in one-to-many relationships
Oct
19
answered Good idea to include summary of frequently accessed data from another foreign-keyed table?
Oct
18
answered Database for a startup
Oct
16
answered Additional fields or intersection table
Oct
10
answered Many to many link tables with history?
Oct
8
answered Data Model: Parent, Child, Grandchild with Child Being Optional
Sep
29
comment Entity with >150 attributes: One table or many tables with relationships
Quite so - even NULL columns occupy space in any RDBMS that I've worked with - if even only 1 bit.
Sep
28
answered Entity with >150 attributes: One table or many tables with relationships
Sep
27
comment Configuration Table
@AnjanBaradwaj - See the edit to the answer. I've copied more of the answer from the SO question. This shows examples of how to use the model to create two different kinds of configuration settings using table-driven rules which won't require any coding changes to your back-end code to be able to read.
Sep
27
revised Configuration Table
Added more (quoted) illustration at the OP's request.
Sep
27
answered How can I integrate a new system to a design scheme I previously created?
Sep
27
comment Configuration Table
@AnjanBaradwaj - The idea is not to use columns, but to use rows. This is how EAV works. Follow this link to the answer on StackOverflow: stackoverflow.com/questions/10204902/… and look at the sample data to understand how this model works.
Sep
26
answered Configuration Table
Sep
20
comment designing multiple tables while eliminating duplication?
@axrwkr - I use Visio with James Martin ERD smart shapes that I built myself and a custom line pattern to give it the hand-drawn look for rough sketches. I find that if a diagram looks too formal some people won't treat it as a rough model, no matter how much you try to convince them otherwise!
Sep
18
comment Optionally free-form reference
Your second approach is a very formal sub-typing schema. It is what you should have in a logical model. Your first approach is a fairly sensible compromise for simplicity and is what you'd likely have in a physical model.
Sep
17
awarded  Nice Answer