Hot answers tagged

113

Distributed Database Systems 101 Or, Distributed Databases - what the FK does 'web scale' actually mean? Distributed database systems are complex critters and come in a number of different flavours. If I dig deep in to the depths of my dimly remembered distributed systems papers I did at university (roughly 15 years ago) I'll try to explain some of the ...


27

OK, first NEVER put tbl in front of a table's name. It's a table, we already now that. That's called Hungarian Notation, and people stopped doing that 5+ years ago. Just call the object based on what it is. If a table holds employee data call it "Employee". If it holds information about computers call it "Computer". If it maps computers to employees ...


23

I would store AB and BA. A friendship is really a two-way relationship, each entity is linked to another. Even though intuitively we think of the "friendship" as one link between two people, from a relational point of view it is more like "A has a friend B" and "B has a friend A". Two relationships, two records.


17

But how far can I continue to break the information down into smaller lookup tables before it becomes unmanageable? I could create a Gender table and have a 1 correspond to Male and a 2 correspond to Female in a separate lookup table. You're mixing two different issues. One issue is the use of a "lookup" table; the other is the use of surrogate keys (...


16

I'm afraid that the reason is simply that the rules were set in an adhoc fashion (like quite many other "features" of the ISO SQL standard) at a time when SQL aggregations and their connection with mathematics were less understood than they are now (*). It's just one of the extremely many inconsistencies in the SQL language. They make the language harder ...


16

Relational databases can cluster like NoSQL solutions. Maintaining ACID properties may make this more complex and one must be aware of the tradeoffs made to maintain these properties. Unfortunately, exactly what the trade-offs are depends on the workload and of course the decisions made while designing the database software. For example, a primarily OLTP ...


14

If it fits within the rules of normalization, then 1:1 relationships can be normalized (by definition!) - In other words, there is nothing about 1:1 relationships that make it impossible for them to obey the normal forms. To answer your question about the practicality of 1:1 relationships, there are times when this is a perfectly useful construct, such as ...


14

Another way (without Nulls and without cycles in the FOREIGN KEY relationships) is to have a third table to store the "favourite children". In most DBMS, you'll need an additional UNIQUE constraint on TableB. @Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you ...


12

We use schemas (think of them as namespaces in SQL perhaps) for both permissions and grouping. So instead of "tbl" etc we have Data.Thing Data.ThingHistory Data.Stuff No code goes into the Data schema. No tables live outside the Data schema. This can extended of course to have a Lookup or Staging schema if you wish. For views we use vw but this was to ...


10

It seems you want to aggregate location based statistics over time for rainfall. A database structure like the one below would let you do that. The 'data source' could be just a filename, or some indication as to where it came from. create table DimDataSource ( DataSourceID int identity (1,1) not null DataSourceDesc nvarchar (100) --...


9

If friendship is intended to be symmetrical (i.e. it is not possible for A to be friends with B but not vice-versa) then I would just store the one way relationship with a check constraint ensuring that each relationship can only be represented one way. Also I would ditch the surrogate id and have a composite PK instead (and possibly a composite unique ...


9

The fundamental answer is that the consistency model is different. I am writing this to expand ConcernedOfTunbridge's answer which really ought to be the reference point for this. The basic point of the ACID consistency model is that it makes a bunch of fundamental guarantees as to the state of the data globally within the system. These guarantees are ...


9

If I want to move record 0 to the start, I have to reorder every record No, there's a simpler way. update your_table set order = -1 where id = 0; If I want to insert a new record in the middle, I have to reorder every record after it That's true, unless you use a data type that supports "between" values. Float and numeric types allow you to update ...


9

This sounds like a really simply one-to-many relationship. For SQL Server, I would write this like: CREATE TABLE Devices ( DeviceID INT , DeviceName nvarchar(255) ); CREATE TABLE Cards ( CardID INT , CardName nvarchar(255) , DeviceID INT ); CREATE TABLE Ports ( PortID INT , PortName nvarchar(255) , CardID INT ); INSERT ...


8

If I understand the question correctly (and I'm not sure I do), you are worried about computing "(Some formula to compute distance here)" for every row in the table each time you do a query? This can be mitigated to a degree by using the indexes on latitude and longitude so we only have to compute the distance for a 'box' of points containing the circle we ...


8

Based on your stated requirements, your model is in pretty good shape. Here are some suggestions for improvement: You don't say so explicitly, so it's hard to say - but it looks like you might be storing the user password directly. This would be very bad! If you look at common authentication databases, passwords are stored in encrypted form. You often ...


8

I think the example is a very theoretical one. I have never seen any real world examples of separate FirstNames and LastNames tables. Of course you can normalize your database this way, but it wouldn't make much sense in most of the cases. Back to the relation. I think there are two relations here: First name - Person 1:1 Last name - Person 1:1 I don't ...


8

The ages are discrete values OK. a Person can have any number of the 18 (assuming 0-17 years old) assigned to their account. So it's a many-to-many relationship? If so, you just decompose your data into third normal form as usual, expressing the cardinality by means of one extra relation. Example follows. The SQL dialect is not necessarily ...


7

In your Employees table, I'd only have a lookup for "Position" because it a limited set of data that can expand. Gender is self describing (say M or F), limited to 2 values, and can be enforced with a CHECK constraint. You won't add new Genders (ignoring political correctness bollocks) The first name "John" isn't part of a limited, restricted set of data: ...


7

I believe he is going for something like a binary tree. I would just include three keys that are tied to the unique id of the same table, one for the left, one for the right child, and one for the parent. i.e.- (very much pseudocode) TABLE tree int id autoinc varchar(16) data_you_care_about int parent_id int ...


7

It isn't necessary to have a surrogate primary key on your clubs_chains table. The combination of the two foreign keys in clubs_chains is adequate for the primary key. You can use a foreign key constraint to ensure that your clubs_chains_paymethod table references an existing record in clubs_chains using the compound primary key. This might be helpful ...


7

The General Advice: When you are starting off learning how to model databases, one of the most important rules of thumb is: Every tangible thing that matters to your system is probably an entity type. This is a really good place to start with any logical database design. If you spend some time up front thinking about what kind of things matter to your ...


7

There are essentially three options (with the possibility of complexity via combination of the three options): Keep each table separate Bring all of the tables together as one major table Use an associative relationship: a base table with the common columns, and individual tables for the non-common columns Here are some of the factors to consider: Do ...


7

Your relation is in 3NF, (and not only in 2NF), since as you say the only non prime attribute is Grade, which only appears on the right hand side of your FDs. The relation is not in BCNF, because the left hand side of the two small FDs is not a superkey. You can, however, losslessly decompose the relation to (SubjectCode, SubjectName) and either (...


7

A relation R is in third normal form if every non-prime attribute of R is non-transitively dependent on each candidate key of R E.F.Codd, 1971, Further Normalization of the Data Base Relational Model It is implicit in the definition of a relation that a relation must have at least one key. Nothing about 3NF or any other Normal Form requires that a ...


6

If each node is truly the same data entity, then the paradigm would still signify one table per entity, and a linking column for the tree traversal where each node is only linked once. For entities that are linked at multiple points in the tree, a separate linking table or a multiple distinct value column would be used.


6

In any case, when one thing hasMany of another how/where does it show up in application and database logic? The only way I can think to answer this question is with another example of hasMany that may or may not answer your question. err, except I should have called the table "comment" but I think the point stands. I don't know that this really helps ...


6

You want three tables for this: Users with a row per user and whatever details you need Groups with a row per group and whatever details you need UsersGroups with a unique combination of UserId, GroupId that only keeps track of relationships This will let you have as many combinations as you like.


6

Most database management systems have a hard limit on either the number of columns you're allowed to use, the number of bytes in a row, or both. So your single table won't work in the general case, because you'll eventually either end up with either too many columns or too many bytes in a row. To find out what the limitations are for your platform, Google ...


6

I suggest you take a very close look at this : http://codahale.com/you-cant-sacrifice-partition-tolerance/ It explains fairly well why "stall all reads and writes" is nothing more than an inevitable logical consequence if you want partitions and the data they contain to be consistent.



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