Tag Info

New answers tagged

1

One of my favorite approaches to problems like these in the context of data warehousing usually takes the form of an agile design practice, where the granularity of a system does not need to always be "rolled up" from the lowest level right away, but can be, as stated in the title of the topic "drilled down" into, as the finer granularity details become ...


1

10K tables can be a performance problem -- Tables are implemented using OS files, and the OS gets sluggish with that many files (etc) in a directory. 91 smells like "over normalization". Do not normalize "continuous" values likes dates and floats. Doing so, leads to terrible performance whenever you need a 'range' query. Usually it is wrong to have two ...


1

SELECT MIN(ProductName), DiscountPercent FROM Products GROUP BY DiscountPercent; You might find the output from this interesting: SELECT DiscountPercent, GROUP_CONCAT(ProductName) FROM Products GROUP BY DiscountPercent;


0

Your best approach is to rethink your design. The better way I can think of is to have one table that holds campaign level info (the name of the campaign, when it was created, by whom, etc). Then another table that holds all your recipient data (name, email address, etc). Finally another table that lists out each actual email (this is what would hold the ...


0

It is also important to know what you want to get out of the system. If you only ever want a stream of attendance events that's one thing. If you want to do ad hoc querying that's something else. If this has pretensions to "Big Data" you'll want yet another design. MySQL may or may not be the best technology for some of these. Should you decide to stick ...


0

How you did delete the edges? Please use DELETE VERTEX, not plain SQL DELETE to preserve edges. Starting from 2.0.x, OrientDB provided a warning to avoid deletion of verices/edges by using SQL DELETE.


3

The question one has to ask is: Will I ever need the address id and the subscriber id more than once? If the answere to this is yes, go with the second approach. If the answere is no you could still use the second approach, but most of the time it is more convenient to use the first one. If the triple ELECTRIC_SUBSCRIBER_NUMBER, GAS_SUBSCRIBER_NUMBER, ...


0

Your attempt fails for multiple reasons. First of all a row is not an array. This construct is just not possible: FOR field IN NEW LOOP ... But there is more. I am not sure I like the general idea. This kind of trigger would run for at least every statement (don't use a row-level trigger for this!), which is quite a bit of overhead. It is also ...


0

Solved: SELECT `conv`.`subject`, `mc`.`message_text`, `mc`.`sentDateTime`, `mc`.id AS "msgId", `conv`.id AS "convId" FROM (`crm_receivers_of_messages` rm) JOIN `crm_messages_of_conversions` mc ON `mc`.`id` = `rm`.`m_id` JOIN `crm_conversions` conv ON `conv`.`id` = `mc`.`conv_id` WHERE `rm`.`receiver_id` = '1' AND mc.id = (SELECT MAX(id) FROM ...


0

Here some real world expirience: I was working on very large database with structure like this one. Back then I decided to use 'Multi-tenant schema seperated architecture' based on this article https://msdn.microsoft.com/en-us/library/aa479086.aspx When you considering to use this design in postgres, take in account that: 1. You need to write a management ...


0

You should create a table that holds the form and it has an form ID and the form name. Then another table that holds the questions for that particular form. The fields would be the formid from your first table and then the quesiton ID and the question. The question id would be continuous unique range of numbers (so if you have 3 forms and each has 10 ...


1

(Not an answer, but some pitfalls that make it difficult to design this for efficiency.) More than half the users will have only one link. Some users will each have over 100K links. More than half the sites will have only one page. Some sites will each have over 100K pages. What does it mean? It means that any form of indexing, compression, etc, needs ...


4

You need one partition for that many records. Not 1000. Certainly not 1000/year. This is not a problem that requires partitioning. It looks to me like you've decided on the solution before fully stating and analysing the problem. Reading between the lines, it sounds like you're implementing a mulit-tenant system and have already decided that partitioning is ...


2

To enforce unique email addresses, I would remove all competing email columns and store them in one central email table for all active emails. And another table for deleted emails: CREATE TABLE users ( user_id serial PRIMARY KEY , username text UNIQUE NOT NULL , email text UNIQUE -- FK added below -- can also be NOT NULL ); CREATE TABLE email ( ...


2

One method is to introduce a PersonType attribute. Using this as a composite key along with a check constraint and foreign key in the Student and Teacher tables will ensure a row for a given person exists in only one of those entities and in the proper table. I can't speak to how well this implementation will get along with EF. There are similar ...


2

Add a student/teacher attribute to Person. Since this attribute is dependent of the key in Person (whatever that is), no Person can be both a Teacher and a Student. Now it is a matter of guaranteeing that a person whose type is student is not added to Teacher etc. For DBMS:s that support queries in check constraints you can do something like: ALTER TABLE ...


1

One way to think of this would be that you have two classes of user which have slightly different rules: deleted and not deleted. Deleted users e-mails may clash, not deleted users e-mails must be unique. Because these two classes have different rules (i.e. constraints), instead of using a flag to indicate whether or not a user is deleted, I would duplicate ...


4

MySQL's optimizer looks only at what indexes are available. There is an exception in 5.6: If you have a subqueries such as FROM ( SELECT ... ) JOIN ( SELECT ... ) ..., there are no indexes on the temporary tables that are created. This used to lead to terrible performance. Now, the optimizer will try out various indexes, and create the best one for the ...


2

MySQL has no such feature. It is left to the user to "normalize" the data either for avoiding having to update multiple spots and/or for saving space. In your example, it is generally not practical to do such with first/last name. But it may be advisable for "locations". It is not practical to dedup names (for example) because the payoff is poor. With ...


0

From your application layer, stamp each query with a timestamp and batch ID. Instead of executing the queries, store them in a temporary table with the format sql_test | timestamp | batchID When you know all queries from a batch have been received, kick off a stored proc that retrieves all queries from the batch, orders by timestamp asc and executes them ...


1

Depends on who is allowed to access your application. Probably not a good idea to give DB access to users since they'll be able to connect to the database directly which is probably not what you want. If its a whitelist, maintain a list of users and if applicable which roles they hold in the database. Run your web application using a Windows account, and ...


1

It depends. There are cases whether one design or the other are satisfactory. If you are doing a lot of queries only on the preferred or default entry, then the second option seems better. If, however, you are normally doing lots of queries for all the entries, regardless of whether they are preferred/default or not, the first option is a little easier ...


0

What I would advise you to do is to Google "open source campsite software" (I got 2 million results). Then download a few of these and explore which one(s) correspond to your needs and then adapt and develop according to your abilities. That's the great thing about F/LOSS - it allows you to see database schemas and code in action. In answer to your specific ...


0

Take a look at what Joe Celko has to say about EAV (AKA OLTP and also MUCK - Massively Unified Code Key - there's a reason that he chose that name!). Avoid this at all costs. However, if you wish to persist in this folly, then download and look at Magento, a commercial (and partially F/LOSS) implmentation of the EAV paradigm. Storing a value as text is ...


1

This is quite normal in database design. If you look at this guide about cardinality notations you will see that one option is zero to one (optional) (one campsite has zero to one campgrounds). In SQL you do it by making a FOREIGN KEY constraint from campsite to campground and not declaring the foreign key id in campsite as NOT NULL.


1

Well, actually having a PK with 5+ columns is not necessarily bad in itself. It becomes bad once the PK is also the clustered index as that one would count as the row identifier and thus would be added to each row in a NC index. This would drastically increase the required space. It would also be bad once you actually use the PK by another FK, as you have ...


3

Real world financial transactions do not follow simple assumptions such as "the client will make one payment per month". Make a TRANSACTION table that records money coming in and money going out along with the details of that transaction, such as where the money came from (which client), where the money went to (which loan), and importantly when this ...


1

Do not store dates in a separate table unless there is something about a particular date that has additional information that you need to store. In general the date is a simple attribute of some other entity, it isn't an entity in itself unless you have a very peculiar application. One exception to this might be a star schema data warehouse where there ...


0

You need to add another table: ProjectContact that will be an intersection between ProjectClient and ClientContact. This will let you say which contacts apply to a particular project. The issue that you're going to have is controlling the integrity of the data. You want to make sure that all project contacts come from clients that are actually on the ...


0

Liam, CPU Thread & Concurrency With 20k+ concurrent users, you will be open to connection concurrency issues in the SQLOS and CPU thread issues. That is something you'd want to handle first. CPU thread contention shows low CPU wait times and low CPU usage, but nothing can get a thread. You can see lots of open queries using sp_who2 or select top 100 ...


1

The closer to reality you map, the more flexible the design usually is (and that usually means normalize it!). I wouldn't worry about orphans if you have your F-key constraints set. In the case of deleting a NIC though, remember reality...did we really set the NIC on fire and crush it into oblivion? If so, then in your example, deleting a NIC will delete ...


2

When ER Modeling was introduced in the 1970s, the intent was to provide a model that was not biased towards one particular implementation. People were constructing relational models of a given project, even if the intent were to implement on a hierarchical or network database. This evolved into what I learned as conceptual modeling, and that's probably ...


0

You are best to start with a transactional database that records the data in as much detail as you can get from the source. You can always summarize and report from detailed data, but you can't go backwards from summary information to detailed information. If you are getting data in the form: transaction_date datetime , pallets_taken_in int , ...


0

Sorry I meant the ansi date of birth by age. Again sorry for not commenting - I am not allowed to until I gain a better reputation.


0

I would have commented here but I don't have a high enough reputation. It seems to me the this is a zero to many relationship. it could be that you need one primary key with age as part of it (pk,fk) and then associate this with the age on an occupation or position table. Or have two seperate tables. Hope this helps.


0

Try searching "single table inheritance" or "class table inheritance". Especially the web pages from Martin Fowler. Also, if you use a technique called "shared primary key", you can reference a person by that person's personid regardless of what occupation the person eventually chooses. In the Plumber table, the primary key is not plumberid, but rather ...


1

The table in question was a rollup/aggregation table. Then it is not only fine, it is "right". And it smells like a Summary table, since it begins with day. Do you have some secondary indexes? Keep in mind that if you are using InnoDB, the rest of the PRIMARY KEY columns will be tacked onto the end of the secondary index. Again, this is not ...


5

There are performance issues with very complex primary keys. And It may not be defending against duplication as well as a simpler primary key might. However, there is one design pattern that frequently yields tables with a primary key made up of six or so components. It's star schema fact tables. If the fact table of a star schema has six dimensions, ...


1

Assuming you're using a 64 bit system, an INT(eger), 2^64 = 18,446,744,073,709,551,616, so if you have an insert every nanosecond, your system will run out of numbers after approximately 600 years - I wouldn't worry too much! :-)


2

The first one is a unique constraint. It can be added to an existing table with: ALTER TABLE ADD CONSTRAINT ... Details in the manual here. It is implemented using a unique index. Per documentation: Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint. A uniqueness ...


0

Switch to PostgreSQL. It allows for stored procedures in many languages, which can validate input before issuing the SQL commands. The user can be given rights to the stored procedure without any other access.


2

The way that your query is running is this; Pull rows from tblUserAction starting with @LastRowID+1 and see if that row matches the right @ClientID. Keep going until you've found 10,000 of them This works well if you don't have far to look, but is lousy for those clients with relatively few records, when it would be more useful to find all the records for ...


1

You should try to remove the join to tblClient, since it does not seem that you use any data from that table. Also, if there is not already an index on ClientId in tblCustomer, you could see if adding it will speed up the query. If you are able to, I would also follow the advice of Max Vernon, and change the primary key to just be on the IDENTITY column. ...


0

The experts don't always agree on the exact meaning of the terms used in conceptual data modeling, or even necessarily on what conceptual data modeling really is. But Powerdesigner's restriction in this case is not unique to this product. Many experts assert that an attribute can only be an attribute of one entity. So if an attribute, say "color" could be ...


1

Depending on how you will access the database, there are different options: Local with single or multiple users: Think first, create later and you will have a good design. STEP 1 1. who will consume this data? that will give you an idea where you should store your data 2. how much storage capacity do you need? you need plan for backups and recovery 3. how ...


0

You can consider using scaledb , it's a new solution for mysql that saves the need for partitioning tables, and sharding and it also gives quite a good performance and enables you to use less RAM memory in you system and easily scale the system as it grows. I am not sure about pricing for small scale system , it might be free or at least affordable even for ...


0

Obviously, you are dealing with the same tables in each user schema. Have you considered inheritance for this? It can give you the best of both worlds for some use cases. There are also some limitations. You can have a separate schema for each user and still search all user tables at once very conveniently. Related: Select rows from table where each row ...


4

Since both PersonID and EmployeeID are candidate keys (and presumably will be implemented with uniqueness constraints) your proposed table design satisfies Boyce Codd Normal Form and therefore 3rd Normal Form as well. That doesn't necessarily make it a good idea. In principle, it's no bad thing to provide alternative identifiers for things but in this case I ...


0

Seems overly complex for what you are doing. Perhaps I do not understand the problem very well. However, it seems you would want data_headers to reference data_references. Maybe you could give a more elaborate description of the actual form and how it is broken out over the tables? Looking at this I want to move the crows foot from data_values to ...



Top 50 recent answers are included