Tag Info

Hot answers tagged

8

Saving your data differently would make that query trivial. Namely: one number per row. Something like: results(Date, Time, Rank, Number) where Rank would be 1 (or zero) for the first number, 2 for the second, etc. (Only if the order has importance, drop the rank if it doesn't.) Then your query boils down to (ignoring reserved identifiers): select ...


4

I think this is the original idea. First thing to notice is that the PK on the LineItem table has three attributes {CustomerID, CustomerOrderNo, OdrerItemNo}, as opposed to just two in your example. Second thing to note is the confusion resulting from the use of the generic id name for an attribute. The CustomerOrderNo should ideally be (1,2,3..) for ...


2

I'll go with a mix of some of these ideas: A related table with a INT mask field for permissions as MickyT suggested. --------------------------------------- | user_id | user_data_1 | user_data_2 | |---------|-------------|-------------| | int | string | string | --------------------------------------- ------------------------------- | ...


2

The "item" shouldn't reference the "customer" directly, because this is implied by the item's "order". So, you won't need the "customer" columns on the "items" table at all. The item's relation to the customer is ensured with the existing foreign key. If orders.id is an identity column, consider removing items.customer alltogether.


2

My first IT job was in this area - basically I was involved in a "fiddle" on the part of my employer to land a big contract which involved convincing the client that we had a functioning and "intelligent" order picking system. It involved alcholic beverages and there are many complex rules about these (tax reasons). Anyway, I just wish that Open Source had ...


2

I always use CITEXT for email, because an email address is (in practice) case insensitive, i.e. John@Example.com is same as john@example.com. It is also easier to setup an unique index to prevent duplicates, as compared to text: -- citext CREATE TABLE address ( id serial primary key, email citext UNIQUE, other_stuff json ); -- text CREATE TABLE ...


1

I'd go with a separate lookup table holding the user_id and the column_name's being public. Whenever a user marks a column as being public, it'll be inserted in that table. If marked as private, it'll be deleted. Then I'd create a itvf function with the user_id as input parameter. Within the function I'd unpivot the result of the Select against the ...


1

You can use this design, which requires one more table: - DropDown: DropDownID PK, Name nvarchar(25) - DropDownOption: DropDownID FK (DropDown) UQ1, DropDownOptionID PK UQ1, Name nvarchar(25), Description nvarchar(155) - Estimate: EstimateID PK, etc. - EstimateOption: EstimateID PK, ...


1

The first, you should read the NORMALIZATION concepts (1NF,2NF,3NF,...) after that you can use them to verify your dependency diagram. So, I'm talking about some basic steps to help you convert ERD, which I often do: 1- Identify objects (objects are WHO, WHERE, WHAT, WHEN ), you imagine that they are exited and can be defined. 2- Identify natural keys of ...


1

Implementing this stuff at an app level is a nightmare. You and your team will have to test, double check and retest code which does EXACTLY the same thing that's been done by MySQL (for InnoDB) for MILLIONS of users over a period of YEARS. Follow the discussion (one of the best threads I've seen on stackoverflow) here. With all due respect to you and your ...


1

Almost always surrogate keys (your "Auto Incrementing primary key") are best in data warehouses. I have seen very few exceptions where this is not the case (but some do exist) - yours does not seem to be exceptional. To answer why would be repeating stuff you can find all around the web, by the giants in the field, for instance: ...



Only top voted, non community-wiki answers of a minimum length are eligible