6,287 reputation
1821
bio website mooseware.ca
location Mississauga, Canada
age 51
visits member for 2 years, 11 months
seen 9 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


May
29
comment Optimum design / structure for product data database?
Have a look at my answer to this question, which addresses your requirement for product attribute values: stackoverflow.com/questions/14194841/…
May
29
comment Optimum design / structure for product data database?
Also, see my answer to a very similar question on SO: stackoverflow.com/questions/11779252/…
May
29
comment Optimum design / structure for product data database?
There should be a third table containing attribute names so that a comparison can be made across products. This is called Entity Attribute Value and is generally frowned upon, but I consider it to be the perfect for product catalogs. See: stackoverflow.com/questions/11779252/…
May
29
comment Optimum design / structure for product data database?
This question has been asked before, and not necessarily appropriately, closed as being primarily opinion-based. See dba.stackexchange.com/questions/49432/…
May
29
comment Optimum design / structure for product data database?
possible duplicate of Which schema is better for a shopping project?
May
26
awarded  database-design
May
25
revised Is this many to many relationship correct?
Made explicit the differences between DMBS with and without DEFERRABLE.
May
25
answered Is this many to many relationship correct?
May
25
comment Should I split these fields or leave as one?
@LeoKing - Yes, precisely. If you have a separate column for house name then you can easily query that way. If on the other hand you kept everything in one combined name field, then you'd be down to using WHERE name like '%stark%' which is always problematic for various reasons.
May
25
comment Should I split these fields or leave as one?
@LeoKing - You could start with one table and then decide later if you want to create tables for additional details beyond the character, such as books and houses. Keeping these potential "foreign keys" separate from the outset will save you retyping your data later and it will simplify querying the data, such as "show me all the Starks".
May
25
comment Should I split these fields or leave as one?
@LeoKing - Blank space is a problem if you have many, many nulls per row or if you have an enormous number of rows or if you have tiny, tiny hard disks. For a table with a dozen columns and a few hundred rows (or even thousand) the amount of "wasted" space isn't going to be worth creating an elaborate technical work-around. I would say: trade off a few KB of hard disk for much simpler, more maintainable code.
May
25
answered Should I split these fields or leave as one?
May
24
answered Why do we not store names in a separate table?
May
24
comment I don't know how to read many to many relationships
It should be noted that the tutorial you cited gets the physical implementation wrong. The relationship from Project to ProjectEmployee should be 1:1,M not 1:0,M. The relationship from the intersection table to Employee is often read "is a".
May
22
comment How do I connect my MS Access database to the LAN?
This is not a duplicate question and it shows lack of understanding of the technology to mark it so. The referenced "original" question has one solution to what this question is asking, but it is by no means the only solution nor even necessarily, depending on the circumstances, a good solution.
May
21
reviewed Approve suggested edit on How do you re-sync the Master MySQL DB with Slave DB changes if the Master goes offline?
May
21
answered How do I connect my MS Access database to the LAN?
May
12
comment Airline reservation system
Unless you're doing this as a limited academic exercise, your model is much too simple. Flights can have multiple segments for example, e.g. one flight might be YEG-YYC-LAS and passengers. There may be passengers flying YEG-YYC and YEG-LAS and YYC-LAS, all on the same plane under the same flight number. Also, this same plane may be flying under multiple numbers because of code-sharing agreements where partner airlines both sell seats on the same flight. There are many other examples of how this model is not representative of real-world commercial airline scheduling.
Apr
23
comment Is this design in 3NF? Why to choose another design not in 3NF
@villamejia - Adding a single surrogate key to the intersection entity that implements a logical M:N is a difficult case. To answer your question directly, it is not a subject for normalization per se. There may be practical reasons for doing this, especially in the physical model and especially if the relationship itself is a parent of some third entity. However, I think it is often seen simply because some people dogmatically apply surrogate keys to every table in their database.
Apr
23
answered Is this design in 3NF? Why to choose another design not in 3NF