Hot answers tagged relational
21
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 ...
12
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 ...
10
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
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 ...
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 ...
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 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) ...
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 ...
6
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.
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
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 ...
6
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
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 ...
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
(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
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
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
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
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 ...
4
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, ...
4
The best thing is to normalize the tables. Create a contact_type table with a record for each contact type. Then create a contact_type_xref junction table that contains the identifier from the contact table and the identifier from the contact_type table. Then load the contact types associated to each contact into the contact_type_xref, and remove the ...
4
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: ...
4
See Date's book:
Date on Database: Writings 2000-2006
Chapter 4: On the Notion of Logical Difference
Salient quote:
you'll be aware that I often appeal in my writings to the notion of
logical difference. That notion is one I find extraordinarily useful
in my own work; it's a great aid to clear and precise thinking… The
intent of what follows [in ...
4
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 ...
4
The major reasons why you would use a one-to-one mapping to break a large table into two are for performance reasons for example:
a) The table has binary/clob/blob data in a frequently accessed table hence slowing down performance since the large columns are handled differently.
b) The table has many columns which are accessed by different queries, hence ...
4
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.
4
My only suggestion is to create stored procedure that performs DML on tag_bundle_relation and restrict all users from manipulating the table directly (it's in a sense 'explicit lock' as you say). I don't see any other acceptable ways to enforce the required restriction. In my understanding your simple solution just adds complexity to the system - you will ...
Only top voted, non community-wiki answers of a minimum length are eligible
