I'm blue in the face from writing (for schema design questions) that "it's an iterative process" and that one has to "a) design your schema, b) programme against it, c) fix errors (and there will be errors) or start again, d) rinse and repeat". Is there any way that we can have a generic schema design answer to which we can point people?
Within a multi-tenant database we have a table that contains some system values and some tenant values defined as follows.
CREATE TABLE [dbo].[ItemTypes] (
[Id] INT IDENTITY PRIMARY KEY
, [TenantId] INT NULL
, [SystemType] BIT NOT NULL
, [Name] ...
The three-level ANSI SPARC Database Architecture suggests three data abstraction levels, namely, external, conceptual, and internal levels.
If I understand correctly, the external level represents the users' views, the conceptual level is the conceptual diagram (ER model), and the internal level...
> I don’t work at Microsoft, so I can’t tell you why they are different. In some cases it was simply because they dropped a prefix for readability but the internal maps didn’t change and XEvents loads the C++ header file into the map, so you get the internal values and not the translated one from dm_os_wait_stats.
> Access to font at 'https://straightforwardsql.com/fonts/CeraCompactPro-Bold.woff2' from origin 'https://www.straightforwardsql.com' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.
@Zikato I get that error when loading the www prefixed version of your site
Most people do a 301 redirect AFAIK so both are served from the same source
How does Stack Overflow (and other web sites) remove the 'www' prefix when it's entered as part of a URL?
Is it a redirect, a rewrite or something else entirely?
Update: I'd specifically like to know in the context of IIS 6
@PaulWhite I think subquery is recognizable to a broader audience. At least for me, I hadn't heard the term derived table until I really got interested in databases.
@Zikato In the interests of full disclosure, I was trying to get my head around how to do it (knowing that a different structure was needed) but then saw the comment and modified accordingly. If you'd made an answer I would have left it
@Zikato The reason I did that is because we need a foreign key across those two columns, so it needs a secondary key. This allows you to enforce that the child table only contains rows where SystemType = 0
@Zikato The SystemType looks redundant but it is required to enforce the wanted constraint. Otherwise a row from the tenant itemtypes could reference a systemtype.
@Charlieface the OP has a UNIQUE (TenantId, Name) so it needs a UNIQUE (Name) in yours to keep that restriction (there can't be two rows with same Tenant and Name)
@SeanGallardy-MostlyRetired Yeah. It's currently pretty windy over here east of Charlotte. Which I assume is some kind of front being pushed ahead of the main storm.
@SeanGallardy-MostlyRetired I've seen this before, but it's like you've blogged. It collects 10s of GB of data per minute on each replica. We just sent it to MS to be analyzed and then you won't get any meaningful reply for months
@Zikato I don't remember who I was talking to before, but the volume is so high and there is so much data that needs to be sync'd between different points that for a small run I was able to analyze it in a few days. Having said that, I left CSS and was 1 of 2 people who could do this stuff. Many have left since; I wouldn't be surprised if no one else knows.
@ErikDarling I had to lookup copse. Wow, that's a word.
Within a multi-tenant database we have a table that contains some system values and some tenant values defined as follows.
CREATE TABLE [dbo].[ItemTypes] (
[Id] INT IDENTITY PRIMARY KEY
, [TenantId] INT NULL
, [SystemType] BIT NOT NULL
, [Name] ...
How does Stack Overflow (and other web sites) remove the 'www' prefix when it's entered as part of a URL?
Is it a redirect, a rewrite or something else entirely?
Update: I'd specifically like to know in the context of IIS 6
