New answers tagged

1

Trivial, even at scale. Some minor, but important, tweaks... INT UNSIGNED is 4 bytes; the 16 is meaningless. It will hold values up to 4 Billion; you probably won't have more than that many users. Do not use OFFSET for pagination, instead "remember where you left off", as I discuss here. For the extra feature, turn the OR into UNION so that you can make ...


3

This is definitely a case where you should use one table IMHO. The entities involved are virtually identical only differing by 1 important field - that makes them very good candidates for a single table. Have the SOA_account_number in the table and set it to NULL if the quote hasn't been approved and have a value set when the approval process has been ...


0

Basically they need a Full Stack Developer, concept I handle very carefully since I don't believe in, out of a student. ftw (for the win, if you will) First of, you say "sql server is what I can get for free", absolutely skipping the vast majority of free dbms (Say, MySQL, PosgreSql... for example). Focus on what your local network environment has to ...


0

Not quite sure about what you're after, but you want a list that is created (or not) by one user and this list can have multiple items. The votes on each of the item will determine what position they are on the list? I can think of it like this: The table Lists contains the data of the list itself and the table ListItems contains the ListID, the Item and ...


0

I think I'm understanding basically what you're after. Are you wanting to record each individual vote or maintain a single value in a +1/-1 fashion? This could be a start anyway: UserLists Table Userid ListId VotingAllowed CreationDate ListItems Table ListId ListDescription InitialSequence Votes


0

There are a few issues that come to mind. A normalized approach (jobs in one table, addresses in another, related only by a foreign key) is desirable from a purely database design perspective. It makes both storage and reporting more efficient. With this normalized schema, however, you'll want to make sure that an address isn't entered in duplicate (for ...


1

The best argument against the existing design is that there's really no good primary key on the table (except perhaps an identity column, which I don't consider a good primary key). That makes it hard to join to other tables. An advantage with your design is that a query can easily identify parcels where you (for instance) have mineral rights but not ...


3

Most likely because the expression 1/10/1971 is interpreted as a numeric expression, i.e. 1 divided by 10 divided by 1971 which is 0 (with integer division rounding values down). Telling Access that this is a date can be done using quotes (not really recommended) or by using a date function, like this: IIf([DOB]<DateSerial(1971, 10, 1), "Mature", "Not ...


0

I suspect that most if not all of your PropertyStrings are only valid for one specific PropertyType and not all of them. In that case you would still have a transitive dependency between PropertyString and PropertyType which can be further normalized. To resolve this, make both PropertyTypeId and PropertyId the primary keys of table PropertyString. (This ...


-1

For me, as per your current structure Design 2 is better. You can implement partitioning, optimized query & structured way to create database/table will decrease the execution time. But some case normalization works better, but totally depends on your database designing architecture.


1

Some clarifications: REFERENCES is the key word used for a FOREIGN KEY constraint (which allows to cascade DELETE or UPDATE). Your database design seems to have logical flaws. rating seems like a detail of the main table restaurant. Since you have a 1:1 relationship, you could just include the "rating" columns in the main table. If you need a separate ...


1

I don't know much of T-SQL but I believe that to compare strings you need the logical operator AND. Try this: UPDATE StaffCopy SET StaffCopy.LastName=IIF(([LastName]='Avon' AND [FirstName]='Sarah'), 'Tyne', [LastName]);


3

Metadata is data that describes your data. For example "Kenneth" is my name. It would be stored in a column called FirstName (for example). The metadata would be things like Variable length string with a max length of 50 First name of the user Last updated on Last updated by Analytics is more How many Kenneth's are there in the data. What is the ...


0

3.65B rows a year is a lot. How many year's data will you keep? PARTITIONing? Don't do that until you have a reason. One reason is that you want to delete data that is over a year old. In which case, use a rolling RANGE of partitions based on month or week. Another thing to consider -- will you need the details after some period of time? Or can you ...


2

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


1

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



Top 50 recent answers are included