If you are looking for a way to create a data model to communicate your design, there are no tools out there specifically created to model json-structured data. However, you could use UML tools or XML design tools to create your models. I’ve been less than satisfied with both methods, but it’s all I have … Read more
I’m not a specialist but the read/write master database and read-only slaves pattern is a “common” pattern, especially for big applications doing mostly read accesses or data warehouses: it allows to scale (you add more read-only slaves if required) it allows to tune the databases differently (for either efficient reads or efficient writes) What would … Read more
Wow I just wrote a big post and SO choked and hung on it, and when I hit my back button to resubmit, the markup editor was empty. aaargh. So here I go again… Regarding Stack Overflow, it turns out that they use SQL server 2005 full text search. Regarding the OS projects recommended by … Read more
Which table is the child in the relationship? Answer that, and you know which table needs the foreign key column, referencing the parent’s [typically] primary key. That’s for a one-to-many relationship… A many-to-many would require you to add a third table, using the keys from both of the two tables as it’s primary key.
As Forum has many topics, and a topic belongs to one and only Forum, you probably want to go with a Forum type attribute annotated with @ManyToOne: @ManyToOne @JoinColumn(name = “forumId”) private Forum forum; See more: ManyToOne and JPA mapping
For simple equality checks (=), a B-Tree index on a varchar or text column is simple and the best choice. It certainly helps performance a lot. And a UNIQUE constraint (like you mentioned) is already implemented with such an index, so you would not create another one. Of course, a B-Tree index on a simple … Read more
Postgres 9.5 or later IF NOT EXISTS was added to CREATE SEQUENCE in Postgres 9.5. That’s the simple solution now: CREATE SEQUENCE IF NOT EXISTS myschema.myseq; But consider details of the outdated answer anyway … And you know about serial or IDENTITY columns, right? Auto increment table column Postgres 9.4 or older Sequences share the … Read more
You make sure one of the columns can only contain one value, and then make that the primary key (or apply a uniqueness constraint). CREATE TABLE T1( Lock char(1) not null, /* Other columns */, constraint PK_T1 PRIMARY KEY (Lock), constraint CK_T1_Locked CHECK (Lock=’X’) ) I have a number of these tables in various databases, … Read more
Foreign keys with constraints(in some DB engines) give you data integrity on the low level(level of database). It means you can’t physically create a record that doesn’t fulfill relation. It’s just a way to be more safe.
Are you looking for something like this? http://sqlfiddle.com/ or http://sqltest.net/