Tag Info

New answers tagged

1

You can go for adding a table called RoleType to store info about the role of employee by adding one more column in Employee table as RoleId Role table as with two column RoleId RoleName here RoleId will be Pk for RoleType table and and will be Fk in Employee table, Now you can add all the roles like Manager, Caterer etc. Don't got your Catalog ...


1

You should probably have a table "StaffHostel" which has StaffID/HostelID FKs. It should also have another field called "IsManager", which can be True/False. That is a better way to represent the Staff - Hostel relationship. Table would look like: HostelID (FK) StaffID (FK) IsManager (True/False) If this is for a Hostel, note that people often don't book ...


4

With respect to your question about using Vertica during the ETL, it's very rarely necessary (although not unknown) to use a different type of database for the ETL. I would not do that unless you perceive a specific need to do so. The only times I've ever heard of this being done due to interactions with legacy data sources. Although @Kermit works with ...


3

I'm going to assume that you already have the budget to implement some data warehouse solution. Just briefly talking about Vertica; it is a load and read optimized platform, and certainly not designed for OLTP. The piece on staging and processing data would need some more thought. Vertica isn't really designed to have data staged, cleansed, and moved into ...


1

I would use The Party Model, so that you can associate a book with an Individual or Organization, not just an Individual. For example, you might want Random House in the role of Publisher. Or Mötley Crüe as the author of the book, not just Tommy Lee. Otherwise, looks good. You might want to consider associating the book and the PartyRole, instead of Party ...


1

No. You should combine receipts and purchases, because they are 1 to 1 correlated - there cant be more than one receipt for one paypal purchase, and vice versa. Same goes for Bank Purchases. Therefore ALL of these basically constitute "Purchases". What you call 'receipt' just seems to be the act of CONFIRMING the purchase, and nothing else. Withdrawals ...


1

First and foremost only ever name the columns you actually need in the select list. Never ever use SELECT *. That said, you may already be in a hole out of which you cannot dig. You could create a view to return only the active rows and "data" columns: create table T(c1 int, c2 int, active bit); create view V as select c1, c2 from T where active = true; ...


2

Building on the answer from @Vérace you can get your usernames like so: Select User_Name from dbo.user WHERE EXISTS (SELECT ul_user_id FROM dbo.user_language WHERE user_id = 'USERID' and ul_iso_code = 'LANGUAGECODE' ) AND EXISTS (SELECT ul_user_id FROM dbo.user_language WHERE user_id = 'USERID' and ul_iso_code = ...


0

Thanks to Erwin Brandstetter on stackoverflow, I have my response: Using joins: SELECT u.*, t.team_organization_id AS organization_id FROM users u JOIN teams_users tu ON tu_user_id = u.user_id; JOIN teams t ON t.team_id = tu.tu_team_id WHERE tu_user_id = $user_id; To get that automatically, I could create a VIEW encapsulating the ...


2

This is a classic situation where you use a joining table. CREATE TABLE user ( user_id INT, user_name VARCHAR(25), PRIMARY KEY (user_id) ); CREATE TABLE language ( iso_code CHAR(2), -- or whatever you want the PRIMARY KEY to be. language_name VARCHAR(30), PRIMARY KEY (iso_code) ); CREATE TABLE user_language ( ul_user_id INT, ul_iso_code ...


2

I'd have a table sit between users and messages with a one-to-one many relationship to both. Call it users_messages for example. Each row in users_messages would describe all the users who have an involvement in any particular message. For example if a message had 3 users involved then the table would have 3 rows for this. Each row linking to the same ...


2

A simple way to do this would be to create a new field in the Messages table called MESSAGE_VISIBLE_TO. This field should be of the same type as the USER_ID field and have a default NULL value. When User1 hits the "Clear History" button, the application would need some logic to update all of the messages between the two people, setting the MESSAGE_VISIBLE_TO ...


0

Incase you need it to deactivate it for a single user, you can add a column named something like DEACTIVE_USER_ID in message table with default value as NULL and make a update for message table when a single user dectaivate it.


0

I've seen the following pattern many times: a single audit table that's used for all audit-related activities. Roughly: Name of the table that was changed GUID of the record that was changed (that is, the GUID that identifies the changed record in its parent table) Name of the field that was changed Original value of the changed field (in the audit ...


0

This is one of the better explanations I've seen for how inappropriately sized columns can affect performance. http://aboutsqlserver.com/2010/08/18/what-is-the-optimal-size-for-variable-width-columns/


3

To me it would make sense to keep them in the same table, providing it isn't going to be an absolutely massive table. Change your table to include another field ParentTaskID which represents the original ID of the changed item: TABLE: Tasks ID int pk ParentTaskID int Name varchar(255) Description text Created datetime DueBy datetime Creator int -> users ...


3

You can create a copy of the original Events table, e.g. EventAudit, with an additional field for the date the change was made and perhaps a field identifying the user who made the change, and insert old values to that table. It needn't have a primary key (although it could): EventID would be a foreign key from Events. This design would enable reporting on ...


2

Such a 1:1 mapping is sensible when part of the table is heavily accessed and part of it is lightly used. It's a good choice.


5

A partial unique index should do this: create unique index max_one_null on item (type_id) where manufactured_until is null; For bonus points, is there a reasonably complex way to guard that the intervals do not overlap for one item type Look into range types and exclusion constraints. They were specifically designed for this problem. Something ...


0

Well, for this part of your question: I'm looking for a way to predict growth of the database given its fields, indexes, constraints (as foreign key), number of lines, and so. I searched on Google, but I found only means to determine the actual space occupied by the database, and can not predict their growth. It's not clear why monitoring ...


0

I would commend alternative 1 to you. Just as having cells as columns a01, a02 etc. is poor normalisation so, too, is having measurements as m01, m02 and so on. In your example you have disguised this by calling them foo, bar and baz, but that is what they are. To address this what you call measurement whould more usefully be called Well: CREATE TABLE ...


0

You can create two table one for saving transactions and one for saving month lookup Something like CREATE TABLE TransactionSales ( TransactionID int, ItemID int, ItemName nvarchar(100), Price numeric, Quantity int, TotalAmount numeric, MonthId smallInt ) CREATE TABLE Months ( MonthId smallInt MonthName varchar(20) ) Now in code enum can be used to ...


0

If you query directly from this table, you can use PIVOT but you need add more column TransactionDate into transaction table. CREATE TABLE TransactionSales ( TransactionID int, ItemID int, ItemName nvarchar(100), Price numeric, Quantity int, TotalAmount numeric, TransactionDate date ) INSERT INTO TransactionSales VALUES ...


0

You can build a view that "unpivots" the table and divides item_quantity and total by 12 for each row. The syntax would look something like this: CREATE VIEW transactions_pivot AS SELECT t.td_id, t.item, t.item_cost, x.m AS month_no, t.item_quantity/12 FROM transactions AS t CROSS JOIN ( SELECT 1 AS m UNION ALL SELECT 2 AS m UNION ALL SELECT 3 ...


1

I sat down with management and said, "alright, what's the goal here?" Once we talked about it, they decided to let me write it in Python and use Postgres, since we use Postgres internally. I was able to convince them of that because of the dynamic libraries and the fact that I can tune the instance to cache everything and keep it in memory. So...a highly ...


0

Shouldn't be too difficult, the below assumes you are using SQL Server. My design would be to have a table called "CurrentLogOn", which would look like: LogOnID (PK - AutoIncrementKey) UserName IPAddress LogOnTime (DateTime, default to GETDATE()) LogOffTime (DateTime, no default) You would then want a second table called "HistoricalLogOn", which would ...


0

Looking at the data you posted, what would be the expected result from your point of view? The fact that users share the same desk/IP shouldn't be a problem as long as both users are reported while they're logged in. You could either use your current table design (a single table with Row_Id, update_time, IP and User) or an aggregated table with Row_Id, IP, ...


1

I can only say that you have my deepest sympathies. Doesn't the person/company who/which is getting you to write a tool under this mind-boggling constraint realise that you'll have to do far more work for a far lesser return than if you used (as you say) "Python, Perl...". Anyway, what you wrote rang a very old bell in my memory cells - I remembered having ...


0

See my answer to this question which is similar in scope to your own question, except that your project is already up and running, so you can ignore the recommendation to use PostgreSQL instead of MySQL if starting from scratch. As Lmu92 points out, the one thing that is critical in a scenario like this is to have a very clear set of procedures about ...


0

You should store all information that is common for all companies in one database and copy the data that are needed to the database that needs the information. Adding a new customer would be done in the common db. After that the information will be copied to company1. If it's needed in company2, then the data will be copied to company2. Make sure you also ...


0

This is exactly the reason for using normalization to limited extent and uafter performance testing. Normalization comes at cost of joins (sorting). Main purpose of DWH on 5NF is to store data safe, not to retrieve it fast. Alternative 1 There is a concept of Materialized View: a view that saved on hard drive. MySQL does not provide it out of the box, but ...


2

All current RDBMS' tables can have CONSTRAINTS on columns. These constraints are checked every time data is inserted into the table. It can also check data against other tables. We know that each Plate Type has certain number of Rows and Columns. We can enumerate all Rows and Columns for each Plate Type. So, when data is inserted, the DB can check if a ...


1

OPTION #1 : Use INT UNSIGNED instead of BIGINT If the fields will not exceed 4,294,967,295, change them to INT UNSIGNED ALTER TABLE part1 MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT, MODIFY COLUMN first INT UNSIGNED NOT NULL, MODIFY COLUMN second INT UNSIGNED NOT NULL; ALTER TABLE part2 MODIFY COLUMN link INT UNSIGNED NOT ...


6

Always start by identifying your natural keys, in this case key. If these are too complex (too many columns) or not stable enough (changes to often), consider adding a surrogate key (such as the ID column you mention). The criteria I use for keys are: - unique - stable - irreducible - complexity - familiarity In many situations there will be a conflict ...


2

My take on this is that you may perhaps want to try using a parent-child dimension, so the model will allow for a theoretically infinited amount of levels. That way, you can connect facts to each level of the hierarchy. Here's an example: CREATE TABLE dimGeography ( id parent_id geographyType geographyCode geographyName PRIMARY KEY ...


0

You don't mention DBMS, so I'll assume it is one that supports window functions (DB2, Oracle, SQL-server, Postgres, etc) select personid, xdate, seqnumber from ( select personid, xdate, seqnumber , row_number() over (partition by personid order by xdate desc, seqnumber desc) as rn from T ) as X where rn = ...


0

(sorry for english. i'm study) Is wery simple. You need have "cardinality hole" structure you need to have 2 column 1) pk = 32bit int 2) order = 64bit bigint (BIGINT, NOT DOUBLE!!!) insert/update 1) when you insert first new record you must set order = round(max_bigint / 2). 2) if you insert in beginning of the table you must set order = round("order ...


5

To counter the points directly: Drupal doesn't use them and gets along fine without them, so why should we? Drupal supports many database layers, perhaps at least one of those does not support FKs and they chose to stick with the lowest common feature set? A great many people do use them, the one data point where people aren't using them is relatively ...


0

Managing and keeping track on data modification (insert, update, delete) can be done quite easy using an audit table with trigger that keep track on every change. While managing DDL (structure changes or procedure code changes) or DCL (grants on objects) do required version control. There are some solutions in the market for database enforced change ...


1

OK - so in your first year, all going well, you might be looking at 50GB for 1k organisations? That's certainly not "pie in the sky". What I'm going to propose is based on my own experiences and other people's opinions may vary depending on theirs. I worked for a company that had fewer organisations but more data, however I think that what worked there may ...


1

Removing foreign keys does not damage data because you are doing DDL to the indexes. Once you do that, data integrity (even for existing data) going down the road needs its integrity tested. EXAMPLE create table parent ( id int not null auto_increment, ... primary key (id) ); create table child ( id int not null auto_increment, fk_id ...


1

I'd rather separate user and authentication mechanism/provider. Application may allow more than one authentication provider (say , logins with facebook, openid, etc). Adding columns/constraints every time you add a new authentication option doesn't sound like a very good idea to me (even though that doesn't happen often, such an action usually requires lots ...


0

I'm not sur how you store username and password in your database. Do you store the data in your own tables or do you rely on the login and password that you need to connect to SQL Server? Assuming it's the former then your assumption of a username being unique is only valid if there's a single 3rd party app (like facebook). But as soon as there are several ...


1

Actually I guess the answer is in your boss's words: 3.He's removed them from existing tables to change things and it's caused data corruption that was only noticeable weeks or months later, on high-traffic/ high activity sites, so he'd rather not use them. FKs removal does not change data, yet, if someone runs queries against the DB, like your boss did, ...


4

Implementing this stuff at an app level is a nightmare. You and your team will have to test, double check and retest code which does EXACTLY the same thing that's been done by MySQL (for InnoDB) for MILLIONS of users over a period of YEARS. Follow the discussion (one of the best threads I've seen on stackoverflow) here. With all due respect to you and your ...


2

Almost always surrogate keys (your "Auto Incrementing primary key") are best in data warehouses. I have seen very few exceptions where this is not the case (but some do exist) - yours does not seem to be exceptional. To answer why would be repeating stuff you can find all around the web, by the giants in the field, for instance: ...


0

Generate ERD with PHPMyAdmin is also a better option. PHPMyadmin added this functionality from version 3.4 detail step to generate ERD : http://goo.gl/0z3vFE You can refer to PHPMyAdmin documentation for more info: http://www.phpmyadmin.net/documentation/#pmadb


9

Saving your data differently would make that query trivial. Namely: one number per row. Something like: results(Date, Time, Rank, Number) where Rank would be 1 (or zero) for the first number, 2 for the second, etc. (Only if the order has importance, drop the rank if it doesn't.) Then your query boils down to (ignoring reserved identifiers): select ...


0

MySQL can handle millions of records just fine and is one of the most prevalent databases available, so you will find plenty of support and tools on the web for it. There is little performance difference between MySQL and other databases like PostgreSQL (another fine Database). I regularly use http://excel2mysql.net to automate the import of 1 million+ ...


1

The first, you should read the NORMALIZATION concepts (1NF,2NF,3NF,...) after that you can use them to verify your dependency diagram. So, I'm talking about some basic steps to help you convert ERD, which I often do: 1- Identify objects (objects are WHO, WHERE, WHAT, WHEN ), you imagine that they are exited and can be defined. 2- Identify natural keys of ...



Top 50 recent answers are included