Tag Info

New answers tagged

0

A solution like class-table-inheritance is probably overkill in this case. It sounds like you need the same data, in the same fields, for sub genres and genres. This can all be stored in one lookup table, the PublicationType table. You need one more field in this table, to record the relationship between sub-genres and genres. I'll call this field ...


1

This seems nearly identical to the following question, and my answer there should be detailed enough to explain the concept (and is just too much to copy and paste here). Don't know how to transform variable entity into relational table For your case specifically I would think it should work itself out to be something along the lines of: Publication ...


5

If you can trust the IDENTITY column to be ever-increasing, I see no reason to store this information. It becomes redundant because it's information you can obtain from data already stored in the table (as you have already shown). Storing it is wasteful in terms of: additional disk space additional memory, since the data will hopefully be in memory most of ...


0

First of all: Be concistent within your columnnaming! In Type you use ID, in Customer you use CustID and in Orders CusomerID. Same with ToppingID vs ToppingCode. And you dont have to use the Prefix Pizza in the Pizzas (imho, plural is also not that nice) table. The relation Type is not clear to me. I guess you can put the OrderType directly into Orders. ...


2

On a storage system that offers RAID data protection, I highly recommend splitting the database into several equally sized data files with one file on each LUN/drive/mount point. That gives increased aggregate service queue depth to reach the data at both the logical & physical drive levels. Without RAID protection provided by the underlying storage, ...


1

May I suggest that you take a look at the treasure trove of Open Source software that is out there in this area. A Google of "open source double entry accounting software" gives several promising avenues of investigation. You could look at the GNU accounting package here, a couple of review sites (1 & 2) and finally the wiki of accounting software with ...


1

A Journal is a chronological listing of all transactions of a specified type for an accounting system. Here is a classical presentation on ledger paper of a simple Sales (on Account) Journal: Note that every line is a single transaction, with Total Debits = Total Credits; and that every transaction hits the same three accounts. A Cash Sales Journal would ...


0

Dan Craven answered the first half of your question, and I agree that it's better to store the path to the file rather than the file itself in your database. MySQL is fully capable of storing the full MP3 file as a BLOB, but can suffer some performance problems along the way. This part is an opinion based question -- there's no universal "right way", but ...


3

The validity depends on how users want to look at the data. You are looking at it as just a transaction fact. Other types of fact tables include periodic snapshots and accumulating snapshots. If you want to see all the times that someone corrected a row to help decrease erroneous entries, the effective dates may be appropriate so it's clear that the ...


6

In SQL Server, you can create file groups that consist of 1 or more files. You can then migrate data into those file groups individually by rebuilding the clustered index for each table on that file group. You can also do the same for indexes by rebuilding the index by specifying the desired file group. You can use file size caps to ensure the files do ...


0

In my personal opion (however I am new to databases as well), I would look to store the relative URL, which would be the VARCHAR from here you could store the realtive path to the file (from where the script is being called from) and load the file that way. However it may be worth writing the SQL for your tables as well. Just incase you need to alter or ...


0

At some point of scaling up you need to buy low-volume high-capacity parts. Low volume is expensive because development costs can be amortized less. There are few systems with 16 sockets in the world. Also, vendors take demand for high-capacity parts as a indication that you make a lot of revenue through this system. They then increase margins assuming that ...


3

In some cases (perhaps most) the servers are already at capacity physically. An increase in the number of CPU's would require a motherboard swap. To add RAM to an existing server could be expensive, depending on how old the server is. Memory modules more than 5 years old and sourced from a dealer can be prohibitively expensive. What all this amounts to ...


0

I believe SQL table should have plural names. It simply reads much better. A table of book records should be called books. The ORM should use the same convention. The Books object is a collection, and presides over all records in the Books Table. A Book object presides over a single record. This makes coding more natural. select name, ...


0

I think the company determines the fiscal year, not the other way around. This is based on my experience with companies. Your experience may be different. Hence, I would put an FK to FiscalYearId in the Companies table, and not put any FKs in the fiscal year table. However, I would put two separate entries in the FiscalYears table for two different ...


1

I am aware that it has been some time since the OP asked this question, but I hope this answer can be useful for other seekers with a similar problem. If I understand the situation well, the part of the scenario that you are confused with is an example of what is called (super)type-subtype1 relationship. Context This kind of relationship shows up when, in ...


0

Based on @dezso and @ypercube comments, I think correct design is: Table Companies: CREATE TABLE [dbo].[Companies] ( [Id] INT NOT NULL, [Code] NCHAR (10) NOT NULL, [Name] NVARCHAR (MAX) NOT NULL, CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED ([Id] ASC) ); Table FiscalYears: CREATE TABLE [dbo].[FiscalYears] ( ...


1

Design A implies a relationship between FiscalYears and Branches which exists separately to the relationships between these tables and Documents and Factors. This relationship is recorded in FiscalYearsBranches Design B does not do this, it only defines relationships between FiscalYears and Branches via Documents and Factors. You should think about the ...


2

Regarding names, consider using double-quotes so you don't have to escape apostrophes in Irish or Italian names (e.g., O'Hara or D'Amato). I'd also recommend getting a good set of Regular Expressions to use, so you can output parts of your name fields (e.g., first initial, nickname, Jr/Sr, etc.).


1

Run the script below (in order). Take a look at the image. You don't need a transaction_fee table - this information is already in the treatment and medical_procedure tables. A few (free :-) ) words of advice. You should take a look at Open Source medical practice and/or hospital management software. You may get some ideas there (even better than mine!) ...


0

I think you're on the right track. I presume questions.type (NB type might be a reserved word and cause problems) will have the check constraint check type in ('multiple_choice','free_text') or something similar. Then all you would need to do is rename your proposed UserAnswers to UserMultipleChoiceAnswers and drop the column answer, and create another ...


1

Cardinality in this sense refers to the number of unique values, usually when referring to a column. For example, in a table containing information about employees, the gender column would be said to have low-cardinality (few distinct values, repeated many times) whereas telephone number would have high-cardinality (many unique values). It seems odd to me ...


1

Design a shared-everything system. This can be deployed in a shared-nothing way i.e. put each tenant in their own database, should that prove desirable. The extra development effort to type the additional predicate is very small and easy to do up front. The reverse - where you re-factor a shared-nothing system to become multi-tenant - will be a ...


2

The answer may be referring to cardinality as in the number of rows within the table. This link has an explanation of each definition of cardinality in a database: http://www.programmerinterview.com/index.php/database-sql/cardinality-in-sql/


0

If you want a history of the all the players that have passed in that team the approach: Team 1:N Contracts N:1 Players is fine. Otherwise if you just want to know the players that are currently in the team and contract is not a needed information for you, the approach: Team 1:N Player will also work


1

Yes. You have two different relationships here. A one-to-one relationship between employees and header_maintenance_report via employee_id on each table. A many-to-many relationship between employees and header_maintenance_report via the keys on detail_maintenance_report. So long as you know what these two relationships represent (e.g. report author and ...


1

Short answer: It depends on a lot of different things. Longer answer w/ questions: If you haven't used any database management systems before, then any of the main options (SQL Server, Oracle, PostgreSQL) will suit your needs. However, do you already have a DBMS installed on a server, or is this a personal project starting from scratch? Do you require a ...


2

If the databases are going to live on the same server, then from a straightforward performance perspective, using a single database divided by schema vs. a bunch of separate databases isn't going to be any different. There is no "cost" to calling database A vs. database B (unless they are both set to auto-close, a "feature" you should avoid like the plague). ...


0

First of all, some tables are seperated but really hold the same kind of data. For example, video, audio and images are all saved as urls, so you can join them in one table instead of 3. This will greatly simplify your schema, and allow you to permit several types of content in one collection (for example, a wedding album that contains images & video). ...


2

Option 3 is classic 3NF design. If you need to add data to the table quickly, it will likely be the highest performing approach for inserts, so if you need to insert the data quickly and have high concurrency concerns that may be the way to go. To me, it sounds like you're trying to solve an analytical problem. If that is the case, I'd go with option 2. ...


1

Option 1 is doable, but not very user friendly and will eat at your CPU when it deciphers your values. Option 3, with properly designed indexes would be your best option.


0

You have the foreign key in the wrong table. Your defining entity is in the MKT_DATA_BONDS table. The refering entity is in the BONDS table. Each BOND must be defined in MKT_DATA_BONDS. The "optional" part is carried out by the fact that not all market bonds need be represented as a company bond. To put it in OO language, market bond is the super or parent ...


0

Remove user_profiles table and keep all user's personal data in one table. the user_profiles table is not storing any data about the nickname itself and you say a user can have no more than one nickname. I assume the nickname is optional. The only reason to have it in a separate table MIGHT be for performance to get a list of nicknames such as when a new ...


0

This might not be the answer but a general tip for designing is to remember that for your initial logical design. A logical relational DB schema is only made up of one-to-many relations. e.g. One Park has many Photos and One User has many photos. That allows for a many-to-many such as; A user may log photos for many parks and a park may have photos logged by ...


1

It's not encrypted. The db-schema is: CREATE TABLE moz_places ( id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date ...


1

Keep them together. You're describing vertical partitioning, which is generally only used when a table is getting really wide - dozens or hundreds of fields. Until and unless there's a compelling reason to separate them (e.g., a single blogger can have multiple profiles, for different blogs, audiences, or languages), there's no need to complicate the ...


0

the problem is that you need to put in a direct way the user, eliminate info DriverManager.getConnection("jdbc:orient:remote:localhost/GratefulDeadConcerts", "root", "password");


0

Can't add comment, but this is query with idea like in MySQL count multiple columns and sum the total occurrence SELECT drawno, SUM(CASE WHEN FIRST = 0 THEN 1 ELSE 0 END) zero, SUM(CASE WHEN FIRST = 1 THEN 1 ELSE 0 END) one, SUM(CASE WHEN FIRST = 2 THEN 1 ELSE 0 END) two, SUM(CASE WHEN FIRST = 3 THEN 1 ELSE 0 END) three, SUM(CASE WHEN FIRST = 4 THEN 1 ...


1

I am not sure what exactly are you looking for. But there is no limit to the number of DB you can create. Refer to the below link if you are looking for the limitations. Number of Databases and Table


1

One issue I have with your current model is that it would be possible for a FavouritesList to contain FavouritesListItems that belong to different Users, which to my understanding of your business logic shouldn't happen. I think a better way would be to store all FavouritesListItems that don't belong to a particular FavouritesList in some entity that is ...


0

Having all languages in one table is an overhead. Each time you will have to load the data of all languages and use part of (for one language), or you will add a condition to your query to load one language's data, which is also an overhead. In my opinion, having each language is a separate table is lighter. However, if the load on your website is low, and ...


1

For convenience, define the (implied) numbering of your question: A->D BD->E AC->E DE->B Then substituting 1 and 3 into 4 gives (A)(AC) -> B which reduces to just 5. AC -> B.


1

As in all things SQL Server, the answer is "it depends". A good database developer can design an application to mitigate SQL Server hardware requirements with attention to detail on database design, index/query tuning, and application design (e.g. appropriate data caching). So it seems you may have covered that part if you see index seeks rather than scans ...


0

We use Dataedo for generating data dictionaries for all our databases - Oralce & SQL Servers. We don't use anything automatic for ERDs - we do them manually (but simple overview diagrams) with Visio.


0

Is the granularity "transaction" or "current balance". Based on your description it seems that there is mix of the two in this fact. If the granularity is "transaction" then you should calculate balance and correct any "transactions" entered erroneously. If the granularity is "current balance" then you have to deal with the difference between correcting an ...


1

I would suggest using two tables, one to store the records uploaded and make an id for it, and another to hold the elements for the record.data types are just for illustration purpose only create table UploadedRecords ( id int not null auto_increament, uploadtime datetime not null, . . primary key (uploaded_record_id) ); create ...


1

What you need is "Microsoft® ODBC Driver 11 for SQL Server® - Windows" which can be downloaded from: http://www.microsoft.com/en-us/download/details.aspx?id=36434


1

Add an index, not a unique key: ALTER TABLE md_metrics_status ADD INDEX (`timestamp`); I believe you are using PHPMyAdmin to add the index, and in PMA, unique is the default choice when you want to add an index, and that is why you are getting the duplicate key error.


0

You can have a table of weights -- that is, an association of user with each question and how much that answer is weighted. Then you can give each user their own weight for each answer. create table Weights( UserID int not null references Users( ID ), AnswerID int not null references Answers( ID ), Weight int default 1, -- For Ux: Ax * ...


2

One table for all users. Period. End of discussion. (This is a common question; the answer is always the same.) When you get to a million rows, we can discuss some other issues and solve them before you embark on the road to a billion. What is best for project X? If it will have a billion rows, you will need a database expert to tweak whatever ...



Top 50 recent answers are included