Tag Info

New answers tagged

1

Just adding files to the existing file group will not redistribute the existing data. I would go the route @Max is suggesting and ensure that the new files you are creating are the same size to maximize the proportional fill feature. And before you begin take a full backup. Once you've migrated all of the tables, indexes, etc over to the new file group ...


1

I think the first solution is good when you know that some instances WOULD NOT participate in relation. But what about second solution? I think it's good when you're sure that all of the instances WOULD participate in relation. The first thing you can do, is analyzing your Problem.


0

Generally speaking, I find it less confusing to use positive terms in design. Here it would mean storing "can call" days rather than "no call" days. Up to you, of course, but it may be worth considering. If you were to normalise fully you would have a Person table and a Day table with values "Monday", "Tuesday" .. "Sunday" and a Callable table with values ...


0

Your schema has a circular reference and redundant columns/foreign keys. Consider the following normalization of your data structure: Put itens as a reference table, no Foreign Keys. Stocklist references Items Users have a FK to Household, no use on including the reverse relation because you can always find the User records for a given Household with a ...


1

I don't know what your professor has really said I am not sure if I understand your model. But maybe the following is interesting. In the article A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model by T.J Teory, D. Yang and J.P. Fry is described how entity relationship diagrams can be transformed to relations ...


1

Learn Table Inheritance (aka subtype/supertype) Individual : Legal Party (":" meaning "inherits") Organization : Legal Party Email : Address Phone : Address MailingAddress : Address WebURL : Address LegalParty -< PartyRelationships >- LegalParty LegalParty -< ContactInfo >- Address Read up on existing data model ...


0

I suggest a MATERIALIZED VIEW with unnested values and a trigram index as search tools. hstore If you don't need nested values, hstore may be even better for you. Use the function svals(hstore) to unnest hstore values. You need to install the additional module hstore once per database: CREATE EXTENSION hstore; Key value pair in PostgreSQL Table ...


1

The problem with a FromDate / ToDate format is that the query generates an index scan. For example, a staff member takes vacation and returns two weeks later. ID FromDate ToDate 15 2011-01-03 2011-01-17 Imagine many staff members, some with 20 or 30 years of such work history, maybe taking only a day or two at a time several times each year. ...


0

First, I think you have misunderstood or are misremembering the professor's point. In a one-to-many (1-m) relationship, if one side is zero, it must be the 'm' side. One is, well, one. By definition it cannot be any other value including zero. And in any 1-m relationship, it is generally understood that "many" includes zero, if for no other reason than the ...


0

Sounds like you need a proper CRM (: For the days of the week I think you have it right, have a column for each day of the week and if a day is a "no call day" you check it off. Your query would then say, "ok it's Monday who can I call?". As far as the adhoc days (on vacation etc). You may just want to have a notes field that shows on each record on the ...


0

The order-invoice-payment pattern is one commonly seen in accounting. Those tables belong together in the "finance" part of your business model. It is well-established practice to be able to tie incoming money back to a customer request. Your financial auditors will expect to be able to follow that trail and reconcile the rows there with other records. ...


0

There is not enough information, but sadly I don't have enough reputation to post a comment so I'll do my best even when I'm making some assumptions. The term subscription suggests a periodic event. Maybe the monthly t-shirt. As it is periodic, it does not generate any order when it is created. When the periodic event is triggered (the monthly t-shirt is ...


1

It looks like you changed the context of the /mnt/db/pg_data directory, but didn't apply the changes to everything underneath. You can verify this by running ls -Z on the files in question. If you don't see 'postgresql_db_t', then the changes weren't applied. restorecon -Rv /mnt/db/pg_data/ should set the SELinux contexts correctly. Forcing a relabel of ...


0

You have a table that contains All (Active and Inactive) Doctors and you have a file that contains Active Doctors. The question then becomes how can you determine who isn't in the file? The SSIS way to do this is to use the Lookup Task, just as you are already doing but instead, you're going to use a Cache Connection Manager to allow you to use the flat ...


2

I want to distinguish carefully between analysis and design. You analyze the subject matter in order to "make sense" out of each of the values to be stored in the database. You design the database in order to come up with the containers (tables) and linkages (foreign keys) that make the database functional and hold the values you discovered during ...


0

my questions is: what software/skills do i need for it to run on the server side and on the client side. (ie..need sql server, asp.net programming skills) While this is not DBA specific question, I will say stay away from Microsoft Access. It was not designed to run web app and shouldn't be used for that one. IMHO, it is dead! Now to answer your ...


0

The answer over here is well written and makes arguments similar to zgguy's answer, that partitioning doesn't buy you much, if any, benefit to a single-machine scenario where the most frequent lookups are predicated on the primary key or something similar (because indexed lookups should be just as fast). In fact, a common thread of advice seems to be that ...


0

One possible way of doing this is to add a text field for non-shared properties, and them is a serialized array format, or a JSON string [Possibly in a separate table]. This comes with a problem though that you cannot index the "keys", so the search will be slow if the table grows big. For example: parts(id, name, quantity, ...) #eg. (1, 'Engine', 4, ...) ...


5

Imagine you are out with a group of friends and the conversation turns to movies. Someone asks, "What did you think of 'The Three Musketeers'?" You respond, "Which one?" What additional information would you need to be absolutely certain you are both thinking of the same movie? The director's name? The production studio? The year it was released? One of the ...


0

This is what I've designed as a solution for this problem but I don't think it's right, due to the tables being linked together in a circle.


4

Your table definition looks reasonable all over now. With all columns NOT NULL the UNIQUE constraint will work as expected - except for typos and minor differences in spelling, which may be rather common I am afraid. Consider @a_horse's comment. Alternative with functional unique index The other option would be a functional unique index (similar to what ...


4

You can generate a customized index table, for example, that has a row for all year&week pairs that the date range (a certain vacation etc) encompasses. Then you can join dateranges by going through that index table. It will be large, but does avoid large scans as you can just list all vacations that have any weeks that are in common with another ...


2

Overlapping date ranges can be found thus WHERE FromDate <= QToDate AND ToDate >= QFromDate You will get one row returned for each partially or fully overlapping range. For example, if you had separate pay rates for morning, afternoon and evening shifts and someone worked all three, there would be three rows returned. The same basic pattern ...


0

Generate a calendar table (that's googleable) and join your other tables to it. This makes it easy to do overlaps, etc. in an efficient way. In case it is not clear this is the standard way. I haven't gone into example usage because it is too involved for an answer here. Search for "calendar table" will give lots of examples and a full explanation to ...


3

The ID column has no advantage at all when it comes to the uniqueness you want/need to enforce. Uniqueness of whatever combination of attributes is never going to be enforced by adding a meaningless ID. Its "advantage" only shows when you ever get to the point where you'd need a new table that needs a foreign key to this one. In that case, and IF you have ...


1

Or you could just create one claim table with columns for the various types and a typeid column linked to a claim type table. Sure, you will have empty or null columns for certain claims but that's not a bad thing.


1

From the comments added to the original question, I gather that the central problem with the legacy design centers around types and subtypes (often called classes and subclasses) of claims. This is a classic problem in relational design, Often, new database designers will have had some formal learning concerning many-to-many relationships, data ...


1

Don't store functionally dependent data. If you should need that for performance, you can always add a materialize view later (see below). But it's often better (and sometimes even faster) to calculate derived values in queries on the fly. In particular, you would create many dead rows, table bloat, more work for VACUUM and nasty concurrency issues in ...


3

There is nothing in your question that would speak against inheritance. One major limitation of the current Postgres implementation of inheritance is that FK, PK and UNIQUE constraints only apply to individual tables, not including children. But that seems to match what you need exactly: Create PK and FK constraints on each child table individually. ...


0

Though reads are more common on these votes than writes. From my understanding you always load the vote count together with the question when displaying it. Therefore I would recommend storing them together. Besides you save money because RAM requirements are lower. Christian


1

Perkinson's mtehodology merges logical(ideal) process model and logical(ideal) data model into one combined ideal model and calculates resources of each access path in a table. In this calculation, peak event frequency, entry point, data volume, cardinality, from entity, to entity, access type & required resources by each access, etc. are needed. ...


0

Sounds to me like the best what to do this would be to use a star schema with a date dimension that has all of the dates in a year in it in an OLAP/MDX tool such as SQL Server Analytical Services. That said if you want to use a relation database you would have to use SQL like the following (pseudo code only) Select v1, v2, date from table t1 where date = ...


0

The fact that you are finding it difficult to explain indicates that there is an issue with the design, the expectations, or the perceived requirements. Are Subscriptions automatically paid for, such as through a credit card? Is that why an Invoice seems unnecessary? Or some other reason. Your current state is: Customers can purchase your products in ...


1

As I hinted in my first comment, you have to move the join in the subquery, so you still have only one row of output per item. Example can be tested at http://sqlfiddle.com/#!9/11b37/3 SELECT items.*, (SELECT GROUP_CONCAT(tags.tag) FROM tags JOIN tags_items ON tags_items.tag_id = tags.id WHERE items.id = tags_items.item_id) AS tags FROM items ...


0

To "improve query" response on reading data, replication will help. You could write away to your primary and read from secondaries to distribute the queries. Also the primary then is relieved of the expensive reads, and can be busy with only writing. Just be aware that there can be some lag on the data. For example a forum, it's not 100% necessary that ...


0

I think this looks pretty reasonable for a system in which a user can add comments or attachments associated with a contract, a specific line of the contract, or a proposed modification to a line of a contract. Since you asked for potential shortcomings or pitfalls, here are a few you might consider: Without knowing much about your system, it seems ...


0

You can use dbms_metadata to export to generate entire DDL of an Oracle schema, given the post I pointed out earlier in comment: How to generate entire DDL of an Oracle schema (scriptable)? Using the same package, you can generate the tablespaces DDL: select 'select dbms_metadata.get_ddl(''TABLESPACE'',''' || tablespace_name || ''') from dual;' from ...


1

Judging by the answer you gave in response to @Ozzie, I'm not sure you can do what you want. You can export the metadata of an 11gR2 database using expdp (Data Pump) and the METADATA_ONLY flag. See http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm This will export the structure of your data. This won't include redologs, controlfiles, ...


1

Although it's all opinion I strongly favour using pull techniques for the following reasons: I think it's easier to keep track of the dependencies by using a purely pull-approach. In a way it's similar to the whole concept of queries on tables or queries on views on tables. It's all "pull", not "push". Most DBMSes have better control of transactions etc in ...


0

At a glance, your method of enforcing the integrity of the lifecycle as you have done seems like a good idea. Using the same key for each table would make it possible to have an entry in a later stage without an entry in an earlier stage. Carr's Law of Data Integrity (which, of course, is just a version of Murphy's Law) is: if it is possible, it is ...


0

Not sure if this is a proper answer, but I'm too low rep at the moment to just comment. I have similar tasks that I achieve by comparing the load table to the target table by stored proc ( execute sql step in your ssis package ). Something like: With Inactive as ( -- returns doctor id's that appear in the Load table but NOT in the target table select ...


1

How do I check to see if the record is not in .CSV file but it exists in SQL database? Have a staging table (e.g. dbo.tmp_DOCTORS or whatever naming convention that you follow) that will first truncate (everytime you load a CSV, make sure to truncate the staging table) and then import the entire CSV. Then you can update the main table by checking if a ...


1

In my opinion you must be, or you need, a DBA. Not a single rdbms can solve problems with big data out of the box. In your case partitions can be useful for example. So data compression, etc... I can just suggest 3 alternatives: SQL Server Enterprise Edition Oracle Enterprise Edition Postgresql And the skills to manage data.


0

The most relational way is adding a day table. You will be able to join room, availability and day to find out which rooms are available for a certain period.


2

You have some Courses: COURSES ------- course_id course_number course_name You need to combine one or more of these Courses with a logical operator: COMBINATIONS ------------ combination_id logical_operator (AND, OR or NOT) COMBINATION_COURSES ------------------- combination_id course_id You may also need to combine some of these combinations (so ...


0

Use RFC 5545 RRules and ExRules to represent recurring events (days/times they can't work) Use pg_rrule to turn recurring events into actual events. Use a materialized view.


1

select a.id, a.msg, a.sender, a.receiver, a.friend_id from msg a left join msg b on (a.friend_id = b.friend_id and a.id < b.id) where b.id is null and (a.sender = 1 or a.receiver = 1) order by a.id desc; Can be tested at http://sqlfiddle.com/#!9/35823/10 It uses "groupwise-max" solution with a left join as can be found for instance in mysql manual ...


1

This is one that all SQL DBA from old time use. EXEC sp_help 'your table name'; If only with single line, you don't even need to type EXEC. Just do sp_help 'your table name'


1

You may also want to consider treating the records in Playlist_videos like a doubly linked list by adding a previous and next field pointing to the appropriate Playlist_videosID. Using this approach, if you want statistics about the list (such as track number, etc...), you may need something more advanced. If you use something like Django, django-treebeard ...


2

It seems that you are involved in a scenario that requires the creation of an auditable database. You may find related information by searching for the terms temporal databases and database history tables. Yes, there are cases in which it is paramount to keep track of the updates that an entity suffers over time, and there are other cases in which changes ...



Top 50 recent answers are included