New answers tagged

0

In most RDBMSs you'll have the ability to set-up column conditionals, which are useful for cases like this. Say your user types are 1, 2 and 3 (res, admin, super respectively). You can set users this way: CREATE TABLE users ( id serial NOT NULL PRIMARY KEY, name varchar(100) NOT NULL, user_type_id int NOT NULL REFERENCES user_types, home_id int NULL ...


0

If you need to add comments to other entities, data normalization is your friend. First, your idea of creating a general purpose comments table is a good first step. The problem with a "comment type" column is that you don't gain any referential integrity with this data structure ensuring that a certain type is only related to one certain entity. Instead, ...


0

You can simply use IN for XYZ, so that you don't need to have one for each relation): SELECT * FROM T1 LEFT JOIN T2 ON (T2.T1_ID = T1.ID) LEFT JOIN T3 ON (T3.T2_ID = T2.ID) LEFT JOIN T4 ON (T4.T3_ID = T3.ID) LEFT JOIN XYZ ON (XYZ.ID IN (T1.XYZ_ID, T2.XYZ_ID, T3.XYZ_ID, T4.XYZ_ID))


0

So initially it's T2[ id PK, name, T1_id NOT NULL, -- because it is hierarchy, T2 can not exist outside the parent (T1). FK1 = T1_id ref T1(id) ] ... and you also say LINK_T1_T2_XYZ[T1.id, T2.id, XYZ.id] with a check T1 should parent of T2 You can migrate keys down the entities hierarchy T2[ id, name, T1_id NOT NULL, ...


1

First off, if your example is ACTUALLY a good representation of what you are trying to accomplish, then you are going about it all wrong.. You have three domains here - one is businesses, one is business offices, and one is products. Table BUSINESS should only contain information about the actual business (Netflix, for example) ... no office information at ...


0

First of all, you say: (TFN → ID is redundant right?) This dependency is not redundant, since otherwise you do not know that TFN is a key, and you lose an essential information. So you must include it in the set of functional dependencies. For phone, if each phone is uniquely associated to a person, and each person can have only one phone, then you ...


2

Since you are planning to DROP a column and the indices that support it, I'll assume that doing so is a product of well-thought plan. Also, I'll assume that you want to drop ANY indice that use the to-be-deleted column, regardless of how it is used. If so, you can build your script based upon something like below. You can put this in a stored procedure ...


1

One would hope that the indexes were created for a good reason and not just to annoy other people who would use the database. Assuming these indexes have (or at least had) a good reason to exist, writing a script to blow them away without understanding why they were created or having a plan to replace them seems like a bad idea. Potentially a better ...


0

If there is a failure, I want to 'ROLLBACK' the transaction so nothing is inserted That happens automatically if any exception is raised inside a function (by you or by Postgres). A function is always atomic and can only be run within a transaction context - which is the main distinction from real stored procedures (currently implemented in Postgres). ...


0

MHA orchestrates the reconnection of slaves as needed. Orchestrator orchestrates the reconnection of slaves as needed. Galera (PXC, MariaDB) eliminates the need for failover by having all nodes writable, plus automatic repair of nodes that were offline. Fabric is another way. Doing the task yourself is possible, but complex, risky, etc, etc.


3

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


15

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


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.


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


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


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

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

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.


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


0

Without knowledge of your schema, query attempted and statistics from explain analyze, any response can only deal in generics. In this sense and in terms of SQL, there are generally two commonly used strategies for dealing with finding missed relatinons: NOT EXISTS and LEFT JOIN x WHERE x IS NULL. NOT EXISTS: SELECT * users WHERE NOT EXISTS (SELECT 1 FROM ...


-1

Another option is to use 4 INTEGER or 2 BIGINT columns.


-1

I would suggest restaurant_table_continued


0

I guess link table expresses some relationship, for example current feature to table availability or plans to deploy feature or history or all of them (requires more attributes besides just FKs). So it may be availability#restaurant_table_feature#restaurant_table for the first case or status#restaurant_table_feature#restaurant_table for the last one.


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.


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


0

The SQL Server DB Storage Engine access the data on the hard drive the same way. It can be the exact same physical file. This is why although it's bad practice, you can have just 1 physical .mdf file and have all of your indexed, non indexed data on a clustered index, and heap in the same physical file. Of course performance and disk space usage will be ...


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


0

The real issue is not nested views in themselves. The real issue is the proliferation of nested views as developers layer additional tweaks on existing views. I have found queries with a nested view 4 layers that actually joined to one of the views in it's definition. Our tendency to take the easy way out rather than analyze and solve a problem is the root ...


1

bloodgroup can be moved to it's own table.. You probably don't need bloodgroup at all in the blood_bag table, considering that it's always going to match the donor's blood type. Likewise, you shouldn't need the donor_id in the transaction table since it's always going to match the blood_bag's donor. I'm not really sure why you have a receiver table at all ...


6

Is there any other way to achieve the same(tables cannot be used anymore) without dropping them? A schema change is a very fast operation - just metadata change is required. The original idea I got was from Aaron Bertrand's blog - Schema Switch-A-Roo. You can follow the steps from my answer here Obviously there are other methods like sp_rename N'old ...


2

Remove the permissions as Phil W. suggests. Also remove the permissions from any stored procedures that use the tables. In SQL Server, (I don't know about others) permissions are chained from a calling object (e.g. the sproc) to the called object (e.g. a table).


0

You may wish to start with http://tdan.com/data-model-quality-where-good-data-begins/5286 Particulary i found following technique very useful when building relationships: Relationship Names Your author studied under Richard Barker and is a strong believer in the Oracle/SSADM[5] standard structure for relationship names. It provides discipline, and ...


5

Remove the permissions on the table from the Role(s)/ Group(s)/ Account(s) that [might] be using it. If anything blows up, put them back [quickly]. Hint: Using a script to do make these changes would be a really, really Good Idea.


11

A couple of other options are to just rename the tables, or if they have clustered indexes, you can disable the clustered index.


3

In Postgres, you could use the basic table definitions you have (slightly different syntax) and add a unique functional index using COALESCE(): CREATE UNIQUE INDEX entity_4attr_uni ON entity(attr_a_id , attr_b_id , COALESCE(attr_c_id, 0) , ...


3

There isn't an easy way (except in SQL Server, see below) to enforce these constraints. And I say "these" and not "this" because they are indeed more than one. You want to enforce uniqueness on (a,b,c,d) when all the attributes are not null. And uniqueness on (a,b,c) when d is null. And uniqueness on (a,b,d) when c is null. And uniqueness on (a,b) when both ...


1

You have a lot of factors to weigh, but here's where I would start: Will the columns for FOO comments and BAR comments be exactly the same? Would you ever need to group all comments on both foos & bars, treated identically (e.g. would you ever need a database query to sort/filter/count both foo & bar comments by date/time)? How likely is BAR to ...


6

You are right, the design allows inconsistencies, exactly what you notice. A ProjectRealm may be referring through Project to a company and through CompanyRealm to another company. This is not uncommon, it appears when there is a triangular or a "diamond" shape in the relationships: Realm Company \ / \ \ / \ ...


3

I would keep them separate. Although the details differ, your circumstance smells a lot like the One True Lookup Table antipattern. By combining you will have problems defining foreign keys (which parent to point at?), amongst other things. Indexing strategy will essentially be about spliting the combined table into its constituent parts. And so on... The ...


1

From your definition the relationship is 1 to (0,1). This is different than a 1 to 1 relationship, and is normally modeled as you have done. Your model is the one I would use. If aggregation child can only belong to one aggregation, you will want a unique id on that column. CodeId is the natural key, however this does not necessarily make it a good ...


1

If the multiple use of NVARCHAR(20) bothers you, change the type to INT and adapt the Code table to have two columns: ID (INT) and Name (NVARCHAR(20)).


1

If this is about real people then child may became a user some time and may have his/her children. Also a child may have two parent. So i'd better have a PERSON instead of USER, a CHILD_PARENT and ACTION this way (pseudocode). The only but essential difference from ypercube's answer is CHILD_PARENT structure. I also feel that proper naming greatly helps ...


5

I see 2 options to implement this. The first would be what you already have, with a minor adjustment, to enforce this part: What I'm seeking is a solution where an action (investment) cannot be made for a child the user has no relationship to. In other words, the father/mother can only invest for him/her or his/her child, not for the child of someone ...


0

Is there a naming convention about how can I name primary columns with a one-to- one relationship? I am sure there are plenty of them. Choose one. For example, I would name the reference CodeREF And if having multiple I would use describtive names as per the objects that I will manifest (RawCoderREF, ReviewedCodeREF)


0

Blockchain derived from Bitcoin is slow and expensive; the amount of data that can be stored in a block is very modest. The mechanisms behind blockchains (distribute ledgers) are intended to provide an incorruptible, highly replicated data store; peer to peer is less an essential feature than a "political requirement" to avoid the appearance of central ...


1

So what you want is an insert, an update and a delete procedure for each table. Then you want a "wrapper" procedure that tracks the inserts/updates/deletes and responds accordingly to errors. A "transaction" would be the batch and "rollbacks" would be explicit. I put these in quotes because you don't want to encompass all of the transactions into a ...


0

I have one workaround going which is making a seperate query for each 1:n-relation which connects DNS and SUM(foo) and one query which has the results of all the queries. Then in the little ERM for the query I connect every DNS entry of the queries. For 1:1 relations in the DB I can just use the standard table in the overallQuery. still looking for other ...


2

I can see no strong reason to avoid having a Ticket table that stores each serial number on a separate row, assuming that makes most sense for the application that will use this database. You should still normalise the overall design to avoid repeated data in this table. "Thousands of rows" is pretty tiny for a relational database: you should easily be ...


0

I have somehow the impression that you struggle with a similar problem as me... Check this thread: Modeling question - Meta and instance I would suggest to review your DB model. I have the impression that it's overdesigned and not smart relational... The foreign key relation of widget < Widget 1; widget < widget2 if you have a widget id x, this id ...



Top 50 recent answers are included