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


4h
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
17
comment Advanced design question related to compound primary keys
Regarding surrogate keys in place of compound keys, there is no consensus on this. It is a holy war issue.
Apr
17
comment Advanced design question related to compound primary keys
The constraint you're proposing doesn't make any practical sense. If the relationship is between 'Product` and User why can only one user within a client be related? If products can only be selected once per client then why not relate at the client level?
Apr
15
comment Relations between a imeid, sim chip, and account from a cellphone company?
IMEI identifies a device. SIM cards have an ID number too (ICC ID). A phone number (SSID) is assigned to one SIM (at a time). So there are lots of layers of abstraction. A person holding a phone has a phone number, but only because the phone (IMEI) has a SIM (ICC) in it and the SIM is assigned a phone number (SSID). Since the mobile operator wants to get paid, the SSID is linked to an account (some operator-specific account ID scheme). Note that an account (might) have many phone numbers. Each phone number probably has one plan (at any given time).
Apr
8
comment Implementing voting system like reddit/StackExchange?
If you want to make it like StackExchange, you'd also have to include a date/time when the vote was recorded.
Mar
30
comment 3 way junction table and redundant value
@caramel1995 - This happens automatically. You don't assign a quantity of two laptops, you assign 2 specific laptops. This would manifest itself as two records in EMPLOYEE_OWNS_ASSET or as two ASSETs with an FK to EMPLOYEE depending on how you model it.
Mar
30
comment Attempting to identify transitive dependency
@crm - Yes. Looks good now.
Mar
28
comment Designing a reservation system schema
Unless your bookings are for very carefully regimented blocks of time (days, hours, half hours, etc.) you're really better off recording what is booked, not what's available. Do you have an efficient, set-based query for calculating what's available, such as one from the answers to this question: stackoverflow.com/questions/9604400/… ?
Mar
26
comment Problem with reaching second normal form in a database
@ypercube is right. Take repeating groups out to get to 1NF. You can't be 2NF if you aren't 1NF yet, regardless of the definition/test for 2NF.
Mar
24
comment Attempting to identify transitive dependency
Product code is a foreign key to the PRODUCT table in your invoice detail. In the product table, product code is the determinant, in the invoice item table, it is just a dependent attribute.
Jan
30
comment Table design for departments with duplicate department numbers
@jeff - The issue is about normalization based on functional dependencies. For a logical model you probably would have separate director and VP tables. That doesn't mean your physical model needs them. It may be, based on what you say about employee-department mapping that the PK of department is a composite of dept_id and manager_id. That would also work and it might solve your problem of how to join employee in. I assume your employee table has a manager_id and a dept_id so that would solve your problem.
Jan
26
comment What's a good way to model user authorization to hierarchical data?
@a_horse_with_no_name - True enough, although I think there is room for discussion about how easily anything can be done with CTEs and there's nothing difficult about maintaining nested sets. I think adjacency list vs. visitation number is really more a matter of personal preference. I expect one could argue endlessly about their relative merits, which they surely have, but in the end it will come down to one's particular situation or priorities.
Jan
20
comment Normal form for a relation where the number of tuples having the same value in a column is the same for all the possible values in that column
Normal forms are about how columns in a table relate to each other (and to columns in other tables). Normal forms don't address how rows relate to each other.
Jan
13
comment Best way to handle column that doesn't conform to one particular type
@lee - There are lots of good books - too many to be on topic for StackExchange. Try Googling around for technical books that include the word "Cookbook" in the title. That's a good indication that it's a collection of patterns/applications/best practices.
Jan
10
comment Store last elements in a dedicated table
Having less data doesn't necessarily mean it will be significantly faster. You have to think about all of the extra reading and writing that your idea would involve. A properly indexed read is extremely fast, even in huge tables. It is a mistake to assume poor performance without actually testing it out.
Jan
9
comment Benefits of using Staging Database while designing Data Warehouse
This question is cross-posted at stackoverflow.com/questions/21010045/…
Jan
9
comment Inventory Management DB Design
@IanAuld - I've expanded my answer as you requested. NOTE: You do not want to use your Shelf_Contents table, unless you mean to allow a bin to sit on two shelves simultaneously! It is enough to have the Shelf_ID on the Bins table. Each bin sits on exactly one shelf but each shelf can hold many bins.
Jan
8
comment Inventory Management DB Design
@IanAuld - It's important to get the terminology clear. When you say "bin" you mean a box without a lid that sits on a shelf. In a lot of inventory situations, a "bin" is a space on a rack where you could put a skid or one or more shelves. As long as we're clear, you want a hierarchy of SHELVES each of which has many BINS each of which can have many BIN_CONTENTS each of which is an intersection between BIN and ITEM and which has a quantity. Don't store quantity on your ITEM table, store it on the intersection table, that way you can have multiple bins of the same item.
Dec
28
comment Best practices for ranking records in flexible way
In addition to the "organic" ranking criteria that apply automatically to all 10,000 records you can also have an override ranking for some products, which takes precedence over the organic rank if it exists. This could be stored on the product table and maintained manually or it could be stored in another transaction table and be edited automatically based on a payment page - there are lots of options. Your question is much too vague to get an answer on DBA.SE. You need to give much more specific information about your problem is you expect specific suggestions.
Dec
27
comment Best practices for ranking records in flexible way
What is the ranking based on? Is the ranking arbitrary? If so, you need to store the ranking on the product table. Is the ranking based on something intrinsic to the product (like alphabetical by vendor, product number, or ascending price)? If so you already have something stored. Is the ranking based on something you can calculate (like frequency of purchase or user views)? If so you need to track that data in detail somewhere and then decide if you want to summarize it periodically for performance reasons. You need to give more information about your requirements if you expect an answer.