Tag Info

Hot answers tagged

23

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


13

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


12

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.


11

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


11

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


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

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


8

Another way (without Nulls) 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 sane) if you don't have Id columns all over your tables ...


7

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


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

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


6

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


6

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


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

FWIW ISBN is terrible as a primary key. For one, what happens if you get a book you want to put up for pre-order, but the ISBN hasn't been assigned yet? What happens when the ISBN changes (yes, this happens!)? What happens when they change the ISBN format yet again? I would say make that a candidate key but use a surrogate for the PK. Adding to that the ...


6

Yes, you'd add all three columns. Assuming they have the same names in both tables, you'd use something like foreign key (Name, BoughtFrom, TimeBought) references the_other_table_name (Name, BoughtFrom, TimeBought) If you decide to use a surrogate ID number, you'll still need a unique constraint on {Name, BoughtFrom, TimeBought}. You can do that with ...


5

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


5

The answer is a "it depends". Not very satisfying but there are many influences pushing and pulling the design. If you have app programmers designing the database a structure like you describe works for them because the ORM hides the complexity. You'll be pulling your hair out when you write reports and have to join ten tables to get an address. Design ...


5

Personally I'm a great fan of the Fanö Bedingung, meaning here that not name is allowed to be the beginning of another valid name. And second the Hamming distance between two names is better more than one different letter. Yes that are rule from predigital times, but they make life easier. And third names must be pronounceable, or you will became mad, ...


5

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


5

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.


5

as far as I can see, you need two separate link tables as these are unrelated, one for linking many books to many authors, and one for linking many countries to many books. linkTable Author-Book idOfAuthor | idOfBook 1 | 1 linkTable2 Book-Country idOfBook | idOfCountry 1 | 1


5

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


5

You want to have something like this: This allows you to have any number of work periods per staff member and client and gives you the details of who worked for who and how long (not to mention when - which is also very important!)


5

(Disclosure: I'm a Microsoft SQL Server guy, so my answers are influenced by that.) To really do it efficiently, there's two things you want: caching and native spatial data support. Spatial data support lets you store geography and geometry data directly in the database without doing intensive/expensive calculations on the fly, and lets you build indexes ...


5

NOTE to people with itchy downvote fingers: I know that OP has asked about MongoDB and the answer that follows is RDBMS. However, if you check out the comments you'll see that I did ask why MongoDB and OP's answer is a presumption of necessity due to performance. Since nobody has come forth with a Mongo-centric answer in four days, I am going to offer my ...


5

The primary importance of first normal form is not that it eliminates redundancy, but rather, it's that it eliminates repeating groups. Instead of having multiple columns of the same kind of data in a record, (0NF) you remove the repeated information into a separate relation and represent them as rows. This is what constitutes 1NF. Tables that have ...


5

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


5

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.


5

It depends on what your priority is. Do you want to avoid work or do you want to adhere to the strictest rules of normalization? Personally, I think it is better to have IsFavorite in the child table, and would be willing to put in the work to make sure that at most one child for every parent is that parent's favorite. The primary reason has nothing to do ...



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