Tag Info

New answers tagged

5

Always start by identifying your natural keys, in this case key. If these are too complex (too many columns) or not stable enough (changes to often), consider adding a surrogate key (such as the ID column you mention). The criteria I use for keys are: - unique - stable - irreducible - complexity - familiarity In many situations there will be a conflict ...


2

My take on this is that you may perhaps want to try using a parent-child dimension, so the model will allow for a theoretically infinited amount of levels. That way, you can connect facts to each level of the hierarchy. Here's an example: CREATE TABLE dimGeography ( id parent_id geographyType geographyCode geographyName PRIMARY KEY ...


0

You don't mention DBMS, so I'll assume it is one that supports window functions (DB2, Oracle, SQL-server, Postgres, etc) select personid, xdate, seqnumber from ( select personid, xdate, seqnumber , row_number() over (partition by personid order by xdate desc, seqnumber desc) as rn from T ) as X where rn = ...


0

(sorry for english. i'm study) Is wery simple. You need have "cardinality hole" structure you need to have 2 column 1) pk = 32bit int 2) order = 64bit bigint (BIGINT, NOT DOUBLE!!!) insert/update 1) when you insert first new record you must set order = round(max_bigint / 2). 2) if you insert in beginning of the table you must set order = round("order ...


5

To counter the points directly: Drupal doesn't use them and gets along fine without them, so why should we? Drupal supports many database layers, perhaps at least one of those does not support FKs and they chose to stick with the lowest common feature set? A great many people do use them, the one data point where people aren't using them is relatively ...


0

Managing and keeping track on data modification (insert, update, delete) can be done quite easy using an audit table with trigger that keep track on every change. While managing DDL (structure changes or procedure code changes) or DCL (grants on objects) do required version control. There are some solutions in the market for database enforced change ...


1

OK - so in your first year, all going well, you might be looking at 50GB for 1k organisations? That's certainly not "pie in the sky". What I'm going to propose is based on my own experiences and other people's opinions may vary depending on theirs. I worked for a company that had fewer organisations but more data, however I think that what worked there may ...


1

Removing foreign keys does not damage data because you are doing DDL to the indexes. Once you do that, data integrity (even for existing data) going down the road needs its integrity tested. EXAMPLE create table parent ( id int not null auto_increment, ... primary key (id) ); create table child ( id int not null auto_increment, fk_id ...


1

I'd rather separate user and authentication mechanism/provider. Application may allow more than one authentication provider (say , logins with facebook, openid, etc). Adding columns/constraints every time you add a new authentication option doesn't sound like a very good idea to me (even though that doesn't happen often, such an action usually requires lots ...


0

I'm not sur how you store username and password in your database. Do you store the data in your own tables or do you rely on the login and password that you need to connect to SQL Server? Assuming it's the former then your assumption of a username being unique is only valid if there's a single 3rd party app (like facebook). But as soon as there are several ...


1

Actually I guess the answer is in your boss's words: 3.He's removed them from existing tables to change things and it's caused data corruption that was only noticeable weeks or months later, on high-traffic/ high activity sites, so he'd rather not use them. FKs removal does not change data, yet, if someone runs queries against the DB, like your boss did, ...


4

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 ...


2

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: ...


0

Generate ERD with PHPMyAdmin is also a better option. PHPMyadmin added this functionality from version 3.4 detail step to generate ERD : http://goo.gl/0z3vFE You can refer to PHPMyAdmin documentation for more info: http://www.phpmyadmin.net/documentation/#pmadb


9

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 ...


0

MySQL can handle millions of records just fine and is one of the most prevalent databases available, so you will find plenty of support and tools on the web for it. There is little performance difference between MySQL and other databases like PostgreSQL (another fine Database). I regularly use http://excel2mysql.net to automate the import of 1 million+ ...


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 ...


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 ...


0

I have had success in general replacing lengthy IN operations with a join against a temporary table. This makes sense because RDBMS are optimized to perform JOIN's as efficiently as possible, and the handling of lengthy IN lists will most likely be done by repeating the query for every value in the IN list. The performance issue you see could easily be ...


0

If you decide to keep a single entry for both sides of a transaction, then by definition you are engaging in single-entry bookkeeping. This may be the most appropriate solution for some simple applications, but be clear that you are losing all the functional and robustness advantages of double-entry bookkeeping, in exchange for a simpler design. Note that ...


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

If you don't know the answer, go with the Surrogate. Here's why - if assumptions are made about business rules, and those assumptions are false or the rules change, your data is garbage. Here is an example: Person, Role, PersonRole current business rule states that a Person has one Role. You make a table that links Person and Role where PersonRole ...


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 ...


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, ...


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.


0

What's the question? Does this make sense? Yea, sure. 1. A product could have more than one attribute Fine 2. A product could have more than one of the same attribute. A shirt is both 'brown' and 'green' Um. If you say so. Sounds a bit strange but that's up to you, I guess. 3. All Attributes and its Values have to be pre-defined (loaded from text file) ...


1

To populate the following table from your geom table CREATE TABLE location ( parentLocationID INT, LocationId INT NOT NULL, LocationName VARCHAR(100) NOT NULL, Location GEOMETRY NOT NULL ); The following inserts should work. Edit: I have added a Buffer to work around a bug with ST_CONTAINS Bug Link -- Insert Top Level Locations -- ...


1

A very basic answer would be to let the developer know what exactly they're working with without needing to assign aliases in the event more than one table is being used. For example, if you're joining posts on comments (I'm not familiar with WordPress but just as an example), and the posts table as well as comments table have the columns title, author, ...


3

There is more than one school of thought concerning the naming of columns or fields. One school of thought says that the name should express the intent of the data being conveyed even if that name is separated from the context where it is found. Thus, a name like post_date or another name like comment_date tells the reader not only that this is a date, ...


0

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table. A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be ...


2

A foreign key is a type of constraint. The purpose of constraints is to make sure that data in tables follows certain rules. You use foreign key constraints to ensure that child data has parent data. You can use it to either prevent orphaning child records or to facilitate cascade deletes or updates.


1

Your location table should have a self-reference. Use the Polygon1 type for your geography. eg: LocationId (Primary key, not null) LocationSequence (int, not null) LocationName (string, not null) parentLocationId (int, can be null) Geographical data (POLYGON , not null) So, your table might have: 1001, 1, World, null, ? 1002, 1, US, 1001, ? 1003, 2, ...


3

If you are trying to measure this by, say, running it in SSMS and looking at the elapsed time in the status bar, be aware that is also measuring the network time to transfer the results from the database engine to the client and can include a significant amount of network variability. You could set statistics io on and set statistics time on. This will ...


1

The cardinality of 2 (male,female) is way too low. Although you are using InnoDB and could have foreign keys, the parent table would have only two rows. It's not worth the processing power. Therefore, do not add another table. Indexing it is not worth it because a cardinality of 2 simply screams at the Query Optimizer "DO NOT USE THE INDEXES". Foreign Keys ...


10

For the query plan itself, you can force compilation every time using the following option on your query: OPTION (RECOMPILE); However I suspect what is happening is that the second and subsequent executions of the query are pulling the data from memory instead of disk, and of course memory is faster than disk. If you are trying to test the scenario where ...


3

A slight variation on Rolandos answer. Rather than a TEXT column, you could use an INT column as a mask value. Using a bitwise OR to test the mask would give you way to determine if the field is shown. select id, case when 1 | mask = mask then col1 end col1, case when 2 | mask = mask then col2 end col2, case when 4 | mask = mask then col3 end ...


1

I have another solution that combines the two ideas You will not need to do joins as my suggestion will not involve another table. You will need extra fields to monitor public columns. Actually, you only need one field. --------------------------------------------------------------------------------- | user_id | public_flds | fld1 | fld2 | fld3 | fld4 ...


1

Store the tenant_id first. When you do this you can enable index key compression. See http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1106790 for the syntax and http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#i14618 for the concepts. In your case, you can do it like this: create unique index mytable_idx on ...


1

One thing you need to keep in mind is what you are using PK for. At the logical level (sometimes called the conceptual level), the primary key is simply one of the candidate keys, chosen somewhat arbitrarily. The purpose is to guarantee that each row is unique, that each row has an identifier, and that no part of an identifier is left out (NULL). For ...


0

Certainly not one table for each document. Perhaps one for each document type. In general you are correct - reading or writing to larger tables typically takes longer than the same operation on smaller tables. There are techniques to help with this - indexing for example. Modern DBMSs can easily handle tables of several million rows, however, so unless ...


1

Assuming a yellow background means that column is part of the primary key, I think Game could use some more attention. First up, homeScore and awayScore shouldn't be part of any key. It is acceptable (though unlikely) that every singe row have the same values for these two columns. Second, id_WinningTeam is can be calculated from the home and away teams ...


2

When you connect to MySQL from the command line, simply run: CREATE DATABASE mydb; What does this do ? This will create the folder /var/lib/mysql/mydb The ownership will be mysql:mysql File permissions will be set by the mysqld process Of course, you need to connect to MySQL as root@localhost Notwithstanding, I would strongly advise you to MySQL ...


2

Yes, most linux system administrators only use the command lines client (or other cli tools to work with the database). Maybe the only other thing you will need for design is a text editor (for writing easily before executing), like vi(m), nano or emacs, and pen and paper. Here it is a first crash course: Create a database: CREATE DATABASE ...


0

Here are some additional thoughts, from somebody who has designed an invoicing subsystem: What is apply_tax1 and apply_tax2? Why is late_fee a decimal? Why is it stored on the invoice? How will you keep track of updating the late_fee? Allowance and charge instructions, including late fees, typically belong to an Order. You do not have the concept of an ...


1

Store the running time as an integer in total seconds. For display purposes you can convert the number of seconds into a string in hours:minutes:seconds format or whatever display format you choose.


0

The DatePart function will extract a specified part of a date/time field. The following example extracts the hour from the current time. =DatePart("h", Now()) Here is a link with more information on the function.


0

1a) If the two insert methods create the same data then making them one table seems to make the most sense. After a bulk load I would recomend running maintenance scripts to make sure that all indexing is up to date. 1b) The only way to know if the bulk insert and the single insert would effect each other in this specific case would be to test it. With ...


1

The theory is all fine and good, but it only starts to really make sense once you understand the practice. The Lawyer's version of the first three Normal Forms is: The Key - there must be a Primary key for every relation being normalized. The Whole Key - There must not be any functional dependencies of attributes on any proper subset of the Primary Key. ...



Top 50 recent answers are included