2
Something I would be worried about with this approach is what will keep me from having a Student_id of 27 for Kevin, and a Teacher_ID of 27 for Mr. Jones? If you absolutely want to avoid this you could use a single base table for Person, then everyone get's their own number.
In keeping the current plan, I would pull the user_id field from the user table, ...
2
You'd have to add the crows feet (or whatever notation you prefer) for us to properly understand the diagram. I choose to interpret it as monotonic top-to-bottom.
top table
| |
^ ^
Left Right
| |
^ ^
Bottom
Fig: Diamond pattern
As such I don't believe it is a circular reference. For that you'd have to be ...
2
There are a couple of scenarios where I'd consider replacing a "real" value with a surrogate & look-up.
First and most common is when I want to control the possible values in a column. I can define a table to hold the acceptable list, give it a surrogate key and reference that key in my main table. This is the classic foreign key situation.
Sometimes ...
2
OK, I won't rant about what is 'wrong' in your schema. I will provide an efficient way to batch the normalization for a batch insert.
I will point out two common flaws when "over-normalizing":
Don't do it if the id is bigger, on average, than the data. Note: INT is 4 bytes; BIGINT is 8.
Don't normalize "continuous" values -- anything numeric or date-...
1
The fastest mode in your case is to use
INSERT INTO..WITH(TABLOCK)
SELECT..
ORDER BY PK_KEYS
With the new table having the same PK defined on it.
This way you don't sort data even if insert in clustered table, and it will be minimally logged in simple and bulk logged recovery models.
Note this will work only if your PK is not identity where you try to ...
1
By defining a foreign key (FK) you are saying that the value I place in this table (the "child" or "referencing" table) must have a matching value already in the other table (the "parent" or "referenced" table). By defining User.user_id as a foreign key referencing both Student.id and also Teacher.id you are saying that the value in User must be in both ...
1
Indexes use space, and a 16 byte key is nothing to worry about.
So you should define the primary key on user_relations on (relating_user_id, related_user_id). If you need to search by relation_type, it won't help to put the column into the INCLUDE list, since such columns cannot be used as filter for an index scan.
I see two options:
In addition to the ...
1
With my original design, this view is simple to implement and it performs well. But if I get rid of the column TableB.price, how would I implement this view
create table TableA (
id int,
v_time date,
price int,
special_price int
);
create table TableB (
table_a_id int,
v_date date
);
create table SpecialDate (
v_date date,
description varchar(5)
);
...
Only top voted, non community-wiki answers of a minimum length are eligible
