New answers tagged

0

If I have understood what you need is: TASK TABLE: ID, NAME, DESCR and other attributes ROUTING TABLE: *ID, IDTASK, INFO and other attributes** ANALYSER TABLE: ID, IDROUTING and other attributes SUBCONTRACTOR TABLE: ID, IDROUTING and other attributes INTERNE USER TALBE: ID, IDROUTING and other attributes if you want to trace better the relations, you ...


2

3 tables is best for the following reasons. You have a lookup table, pulling data from the lookup table is faster. You can Expand the employee table without worrying the effects on the language table You can Expand the language table without worrying the effects on the employee table. You can apply a two column key on the lookup table, making it ...


0

As I said in my previous comment, you should have two main tables. Observation (the origin of all cases) Clinical_case (linked to an observation - note that "case" is an SQL keyword). It appears to me that you are overcomplicating things with so many tables. I think that any report made by a health professional should have the same status - be it a ...


1

Your question is less about whether to use many-to-many relationships and more about the choice between a surrogate or natural key. You do need three tables - Employees, Languages and Employee_Languages - but you could choose to use the Language Name as a natural key instead of Language ID as a surrogate key. The choice of whether to use a natural or ...


0

I've only quickly looked at this but my first thought is to utilise NULL attributes in your Users table for EmployeeId and ClientId. They can then be populated as required and defined as Foreign Keys. UserName is the Primary Key for the Users table. Use the UNIQUE attribute on the EmployeeId, ClientId and the composite EmployeeId+ClientId within the Users ...


1

First solution (3 tables) allow you to search of employees with given language in efficient and unambiguous way. The same language name is assigned to different employees (without literal errors like "english"/"Engliish"). It's a better way for developing new functionality in the future.


0

It may be useful to view your case as two problems, rather than one. The first problem is a class-subclass (or, if you prefer, type-subtype) problem. Employees and Clients are clearly subclasses of some superclass that you have left unnamed. I'll call them Persons. (See the subtypes tag). The second problem is the one-to-one relationship between Persons ...


3

Your 2 table design approach can be implemented using a view. This would not compromise your underlying database design based on 3 tables. Views are under used but often give the best of all worlds. Expanded: From your "normalised" 3 table design, which looks OK, you will CREATE VIEW (SELECT Employee ID, Language Name, Proficiency FROM Employee, ...


0

It sounds like what you are looking for is a "unique" constraint: ALTER TABLE Table1 ADD UNIQUE(Series, Orders) That will cause an error to be raised when you try create a row (by inserting new or updating an existing one) that duplicates an existing combination of Series and Order. Depending on the rest of the database design, these columns might be a ...


1

First of all, let me just say (as others have pointed out), that there is nothing wrong with having mutually-exclusive foreign keys! Second, despite it's elegance and applicability to other scenarios, I don't think the superset solution posted by @Joel Brown is appropriate for your exact case. Although it makes it much easier to deal with sub-types of ...


0

I would guess that position is not really part of a relation, but rather an ordering key used to denote what book is the user's first, second, third and so on. Or, like you say, it could be that a user can have multiple copies of a book, but that still wouldn't make position part of the relation, only of the primary key of UserBooks. Or, a combination of ...


1

It is acceptable to have mutually exclusive attributes in a table. If you only have one pair of such attributes, this may be the most practical solution. However, some people may look at your situation as a sub-typing issue. In this view, you are missing the superset entity. You have a collection of (something), some of which are employees and some of ...


0

First of all, kudos on your effort at designing a DB for this problem! It's great to find people from different backgrounds using software to solve their problems. Now, for the meat. For starters, generally for database (or any kind really) design problems, there can be many possible solutions. Depending on the requirements and constraints for a ...


1

So, just to clarify: You want 1 Report to have Many Observations. The Observations then become either a Case or NoCase. Now, each case/nocase is linked by the ObservationID and the ReportID (or the Observation itself belongs to a ReportID), right? It wouldn't be a circle: Report->Observations->Case/NoCase (I can see the Case and NoCase entities as one ...


2

Todd's answer is excellent. Here's an over simplified answer. There are pros and cons to normalization, beyond 1NF. The biggest pro to normalization is that it prevents mutually contradictory facts from being stored in a database. when the same fact is stored in more than one place, it becomes possible to store mutually contradictory versions of that ...


2

As mentioned in the comment by jkavalik constraints are there to enforce data integrity. While most modern optimizers can use the information in constraints to help make access decisions that is not their purpose. Here is a question you need to ask yourself - if the integrity of the data is not important - then how important is the data? If it is worth ...


3

There is absolutely nothing wrong with joining on columns that are not PKs/FKs. If you are concerned about efficiency then the key is to have appropriate indexes defined to support the join operations you are using. Also, don't assume that the existence of a foreign key implies the existence of an index - some databases automatically create such an index but ...


0

TL;DR -- explain how db's work Long version: You can't show that removing a FK can not cause corruption, because that is not actually true -- theoretically it could. What you can show, is that it doesn't cause corruption and explain why the chance of it causing corruption is more theoretical than actual. I would start with why the chance of it actually ...


1

Let's think out of the box. Instead of having a "snapshot", let's have a "log". What you currently have is "current" state of things; adding a "log" would provide the "history", from which could be derived the 'lost' info. One way to implement the log is to have a TRIGGER on INSERT or UPDATE of the table, and have the trigger write to the log file. This ...


3

Here's one approach: create table users ( username ... not null primary key , <additional attributes> ) engine = innodb; create table rooms ( room_name ... not null primary key , creator ... not null , <additional attributes> , constraint ... foreign key (creator) references users (username) ) engine = innodb; create table conversations ( ...


1

I don't know PHP or MySql but the answer is definitely not creating new tables for each room. I'd have one table for rooms and one for messages and have the messages refer to the rooms (via foreign key). You probably want to consider archiving and/or purging as the data will probably grow quickly. Look into MySql's ability to partition tables and if there ...


5

We have defined via comments the following significant facts about your scenario: A Product can be offered by zero-one-or-many Suppliers, and a Supplier offers one-to-many Products. A Supplier and a Vendor are one and the same entity type, i.e., they represent the same set of things that have identical attributes. An Individual (or Person) can never ...


2

The important thing about the 2NF is that in each (non trivial) dependency the determinant should not be a proper subset of a key. In the example, the determinant of AB->C is the full key, while the determinant of C->D is C, which is no part of any key. So the schema is obviously in 2NF.


1

The correctness of a database design is specific to the requirements that you gather prior to building it. In simple terms, one size does not fit all and in many situations you will find more than one solution to your problem. The skill is in determining which solution best fits the requirements that you have. There will be occasions when the solution you ...


3

Mark all your existing rows as old: ALTER TABLE integrations.billables ADD COLUMN is_old BOOLEAN NOT NULL DEFAULT false; UPDATE integrations.billables SET is_old = true; And set up the constraint to ignore old rows: ALTER TABLE integrations.billables ADD CONSTRAINT cc_at_least_one_mapping_needed_billables CHECK ( NOT(("qb_id", "xero_id", ...


5

Your CHECK constraint can be much simpler: ALTER TABLE billables ADD CONSTRAINT cc_at_least_one_mapping_needed_billables CHECK (qb_id IS NOT NULL OR xero_id IS NOT NULL OR freshbooks_id IS NOT NULL OR unleashed_id IS NOT NULL OR csv_data IS NOT NULL OR myob_id IS NOT NULL) NOT VALID; Or even ...


3

Your question really is not about normalization. Instead it is about specialization vs. generalization with respect to design. Let me give some background to show why this is the case. Background A table is a relational (R-Table) table, and thus normalized (meaning in 1NF by definition) if in its design a discipline is followed that ensures: Distinct, ...


10

Just add the constraint as NOT VALID From the manual: If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, [...] and they'll fail unless the new row matches the specified ...


9

If you have a serial column or an integer one that's automatically populated with a nextval (so that you are never supposed to insert new rows with an explicit value for that column), you could additionally check whether the value of that column is greater than a specific value: ( (("qb_id" IS NOT NULL) :: INTEGER + ("xero_id" IS NOT NULL) :: INTEGER + ...


4

This was not possible in Postgres up to version 9.1. From 9.2 onwards you can define a check constraint as NOT VALID (equivalent to WITH NOCHECK in MS SQL Server). See http://www.postgresql.org/docs/9.2/static/sql-altertable.html for more detail. I'm not generally happy with this sort of thing where it is at all possible to avoid. A compromise if you have a ...


1

The short answer is "no." Entity relationship diagrams can be used to show constraints based on the relationships between entities. However, the particular constraint you want to show (that bookings do not overlap in time) is a constraint between rows in the same table, not between two or more tables. The only single table constraint that can be ...


2

If (post_id, user_id) is unique, then jettison the id and make that pair the PRIMARY KEY. I specifically suggest that order because I assume this query is frequently used? SELECT COUNT(*) FROM tbl WHERE post_id = ? AND visible; By having the PK start with post_id, you get the added efficiency of "clustering", thereby drastically reducing ...


4

The session information function pg_trigger_depth() would solve your problem with trigger recursion. Requires Postgres 9.2 or later. The manual: current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger) Best used it in a WHEN clause to CREATE TRIGGER: CREATE OR REPLACE FUNCTION ...


2

You should consider the balance between how often your data will be accessed vs how often your data will be updated. Adding another condition on your SELECT just to display your content may well create more load than deleting rows in the infrequent occasion that a user un-likes. Note that you will need an index on visible to efficiently query against it.


5

The right way to do this will probably be to have your application INSERT via a stored procedure (or in Postgres a function). If stored procedures are not an option, create a view and rewrite inserts against that view so that they affect some other table. CREATE TABLE t ( a integer, b integer ); CREATE VIEW v AS SELECT t.a, t.b FROM t; CREATE ...


0

So, you have the following entities: Hotel, Room and Booking, right? Each Hotel has N Rooms, that's a 1-Many relations. Now, is the relation Room-Booking a 1-1 or 1-Many? I believe that in the 'Room 1-Many Bookings' model, the relationship itself could have an attribute with the specific Date-Time of each booking. Using the 1-1 relationship, you'd have ...


0

You could programmatically build queries to test which combinations of columns look like they might be keys. In order to test a given relationship you'd run a query of the form: SELECT 1 AS [IsPossibleKey: ChildTable.KeyColumn => ParentTable.PrimaryKeyColumn] WHERE NOT EXISTS ( SELECT * FROM ChildTable c WHERE NOT EXISTS ( SELECT * FROM ...


2

Given your "irregular" data, you might want to look at Magneto (see here and here). Originally designed for the clothing industry which can have many different products with many different attributes. It might just be a fit for your needs. There is a community edition so you can look at the code and (esp.) the database table structures and adapt to your own ...


2

There are a couple of misunderstandings floating around here. OIDs were included with every row by default every row in very old versions of Postgres. The default was soon changed to not include OID columns in user-defined tables. Quoting the Postgres 8.1 documentation: default_with_oids (boolean) This controls whether CREATE TABLE and CREATE ...


2

OIDS From Docs http://www.postgresql.org/docs/9.4/static/datatype-oid.html The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is ...


2

You want to track changes to the items at the item level so your item table would have a key that represents the 'item_id' and something else that distinguishes each state change for that item - you could use a timestamp or a counter or a flag. Let's generically call it 'version attribute'. Table Design [LIST] LIST_ID LIST_NAME -- other ...


7

I would suggest two fundamental tables for this purpose, if you're creating this all from scratch. This assumes that this is being created in PostgreSQL. An employee table containing their name and whatever other details are important for your application, with a numeric primary key. The primary key is important because you may have more than one employee ...


2

If I understand your scenario description correctly, you want to manage two different facts, so you should have (a) one table to store product data that is independent of sellers and (b) another table to retain product information that comes into play in the context of each relationship between products and their corresponding sellers. 1. Logical model I ...


0

This is why many industries enforce SKU, UPC or any other unique identifier. If you don't have one, you could create one and let them enter this information in the database. Once the information is there then it will be easy to run the queries you need.


2

My first reaction is that your primary key is not sufficient to guarantee uniqueness. I think you should consider a composite key of ProductId, SellerId. Even then you could still have duplicates in the table. I think you really need to backup and look at the real business requirements. For example just how "faithfully" do you need to capture unique ...


1

Personally, I would split that table. Definitely consider Rick James' point about ensuring everything is Indexed properly and is being queried efficiently first, but, at the end of the day, you're removing 90% of the data that you have to sift through to get what you want. Archiving that data will make queries faster by reducing row counts and shrinking the ...


1

While the specifics will depend on the database system itself this could be handled with pretty much any of the RDBMS platforms out there. Permissions will pretty much handle what you want. The process that puts the data in would need to have insert permissions and all other access would be select only. As far as tracking the modifications - most of the ...


1

A proper Postgres schema for your example could look like this: CREATE TABLE selected_task( selected_task_id serial PRIMARY KEY -- surrogate PK , user_id int REFERENCES users(user_id) , task_number int , name text , jobsite_id int REFERENCES site(jobsite_id) ); CREATE TABLE sub_task( sub_task_id serial PRIMARY KEY ...


6

At this point, I guess SQL Server with an AlwaysOn cluster will satisfy HA, but what about read/write? I think this SQL Server 2012 whitepaper will be a good read for you at this point to get a feel for what's possible with the two different features (Availability Groups and Fail-over Clustered Instances) that come under the AlwaysOn brand name. ...


1

This is an alternative solution that is similar to Rick James' answer, but with a way to avoid app walking, and enjoy a fixed number (1 or 3) of statements to UPDATE the entries in all 3 applicable levels: Create a table for each of your well-defined levels of event (I will call them top, middle, and bottom to keep this generic), so that bottom has field ...



Top 50 recent answers are included