Hot answers tagged

14

From Express Edition: SQL Server Express includes 10GB of storage per database and from Features Supported by the Editions of SQL Server 2014, Cross-Box Scale Limits: Maximum relational database size: 10 GB This latter reference makes it clearer that the 10GB limit per database is applied to relational data, so it excludes log files* as well as ...


7

3 foreign keys version create table tRegions ( ID int not null primary key, name varchar(20) ); create table tCountries ( ID int not null primary key, name varchar(20), regionID int, --any superset of PK is undoubtly UNIQUE constraint cu1 unique (ID, regionID), foreign key (regionID) references tRegions(ID) ); create table tUsers ( ...


4

Yes, the design looks great. Minor notes: You could use TINYINT, instead of the INT for the ClassTypeID. Or even CHAR(1) and have 'A' and 'B' instead of 1 and 2. 1 byte instead of 4 means you are saving 3 bytes in every row, in all 3 tables and in every index that includes ClassTypeID - which would be every index on these tables, if ClassTypeID is part of ...


2

It is possible to ensure tUsers.regionID matches tCountries.regionID when tUsers.countryID is set if you agree to introduce some redundancy in the form of an additional unique constraint (and the index supporting it). The idea is as follows: Make sure tCountries.ID and tRegions.ID are the PKs of their respective tables. ALTER TABLE dbo.tCountries ADD ...


2

Removing permissions is not generally going to work because you can't be CERTAIN that someone doesn't have permissions. Possibly through a group, role or even because they are sysadmin (although let's hope not). For tables you can disable them. And that is a quick process. However to enable them requires you to rebuild them and for a large table that ...


2

Excellent points by @joishi-bodio. Here's a simple diagram explaining the different entities, attributes, and their relationships in your model. The first step in modeling any database is to identify the real-world entities; the attributes that define each entity; and then their relationships and cross references. Here are different observations and ...


2

I think the complexity of performing this task is small and does not change with size. The same SQL that will find a missing recipient from 100 users will also find a single missing recipient from 100M users. The time to do so, however, is likely to be linear in the number of posts and the number of users. I can see two ways to organize the data. One is a ...


2

Rather than making sure tUsers.regionID matches tCountries.regionID when tUsers.countryID is set, you could simply prevent both countryID and regionID from being set simultaneously on a row by adding a check constraint like this: ALTER TABLE dbo.tUsers ADD CONSTRAINT CK_CountryRegion CHECK (countryID IS NULL OR regionID IS NULL) ; Now, if a country ...


1

It's not really how SQL Server works and I think you're going to see some underwhelming real life implementations of the data distribution process. Many data publishers (post offices, automotive and parts suppliers) will publish a set data format document along with full monthly data extracts usually in a CSV or similar text format followed by ongoing ...


1

This is generally developer's choice but in my opinion restaurant_tables implies one-to-many relationship. When a table resolves many-to-many relationship I prefer names like restaurants_X_dishes. Again, this is up to my taste.


1

Do I have to write SQL for that manually? Yes, but it's not that hard: create table original (id integer, url text); insert into original values (1,'VeryLongURLText'), (2,'VeryLongURLText'), (3,'LoooongURLText'), (4,'LoooongURLText'), (5,'LoooongURLText'); create the dictionary create table dictionary (id serial, url text); insert into dictionary ...


1

The second option should be the fastest. It was made for this. Also it should have no bugs since it is used a lot and already for a long time. If you go for this then you do not even need to use a composite primary key. In my opinion the only reason to use the first option is if you need a numbering starting from 1 per client.


1

None of them are redundant. It's ok to have a loop. You seem to be missing a many-many relationship (Employee Course) table, though.



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