Tag Info

New answers tagged

0

SQL Server can perform merge joins. It may choose to read an appropriately ordered index or perform additional sorts on intermediate data.


1

Using INFORMATION_SCHEMA.COLUMNS, here is my proposed query SELECT B.* FROM ( SELECT DISTINCT table_name FROM ( SELECT table_name,column_name,ordinal_position,data_type,column_type,COUNT(1) match_count FROM information_schema.columns WHERE table_schema IN ('db1','db2') GROUP BY ...


0

In Oracle you can use function based index, which can be used to create a kind of "conditional index". CREATE TABLE ARTICLE ( ID NUMBER, SUPPLIER_ID NUMBER, SUPPLIER_CP VARCHAR2 (10 ), NAME VARCHAR2 (10 ) ) / CREATE TABLE contact_person ( SUPPLIER_ID NUMBER, person_name VARCHAR2 (10) ) / CREATE ...


1

Think about how what kind of queries and reports you want to run against your data. Lets say for example you want to know how many students were absent in the month of 2013-10. Here is how that would look with each table: SELECT COUNT(*) FROM TABLEA WHERE DATE BETWEEN TO_DATE('2013-10','YYYY-MM') AND TO_DATE('2013-11')-1/24/60/60 AND STATUS='X'; SELECT ...


1

I would suggest something different than you have here- a separate table to record the student_class relation: | student_id | class | date | ----------------------------------- | 2 | 7 | 2013-10-24 | This would have one record for a student where a class was attended. For each class and date for which there is no record, it can be ...


0

There could be many way possible to store data for such scenario, as with your design attempt it seems that you are looking to have unique Id to identify each product and if that is the case the you are right with having a productstable and make that product_id an auto increment key. Now the next thing to make sure that what ever the attributes of all these ...


2

Yes, create a new record for every reading. MongoDB and other document stores are designed for efficient retrieval of de-normalized or irregularly structured data. A MongoDB collection is essentially a set of key-value pairs with a rich value type. Updating the value is an atomic operation, which means that constantly appending to a list within the value is ...


0

I worked it out, in-case anyone would like to know the following Query did it: SELECT CONCAT( 'SELECT `ost_ticket_event`.ticket_id', GROUP_CONCAT(' , `t_', REPLACE(state, '`', '``'), '`.timestamp AS `', REPLACE(state, '`', '``'), '`' SEPARATOR ''), ' FROM `ost_ticket_event` ', GROUP_CONCAT(' LEFT JOIN `ost_ticket_event` AS `t_', REPLACE(state, ...


1

The basic answer is yes, it is possible to chain is-a relationships. In your case the relationship between Product and Product type 1 or Product type 2 is a case of subtypes. See the questions tagged with this tag for answers to previously asked questions. The relationship between Item and Product is, perhaps, a trivial case of subtyping, because there ...


2

I see from your diagram and from the columns named lft and rgt that you are using the nested sets technique for organizing the categories into a tree. So far so good. Now all you need to do is add a foreign key to the products table that references category id in the categories table. You might as well call it "category_id", same as the column it ...


1

The optimal way to handle it is to table-drive the things. If you have 300 of them now, what are the chances that you're going to have 301 tomorrow? Instead of making frequent schema changes, just keep a table of OWNABLE_THINGS and use a many-to-many relationship to track which things your objects own. Here are both the conceptual view, and the logical ...


1

In your first option, will there be a one-to-one between id_place and reference? If so then it adds no value. Instead, it will complicate your SQL and may make for a less efficient query plan. The reference should be omitted and your second suggestion is the way to proceed.


1

"Just" 300? That might not really be a problem, actually. How many rows do you expect in the table? If not that many, say, less than a million, then doing that might not be a huge problem. Or if you're using PostgreSQL, as noted, you can use an array of booleans. That, I think, would be preferrable to putting each column individually in the schema. The ...


1

I don't see there is much more difference in the design and given the choice I would choose to have a single table to store these picture. Single table removes the need to have JOIN in sql query and will improve the performance. In a single table structure you can create Indexes depending on your sql query used to read data that will improve the ...


0

Assuming that you are prepared to compromise on the absolute correctness of the answer in order to obtain practical performance, you could do the following: Create an index on score (assuming descending, but either way is workable) Create a maintenance job to rebuild this index periodically (how often depends on your willingness to have your rank results ...


6

Databases typically implement a lot of the OS style functionality themselves. At a high level, this is done to improve performance and scalability. To give you some examples: Operating system file caches are generic, least frequently used, style caches. A database needs much finer control of what goes into the cache and what does not. For example, when ...


0

For some projects a Rules engine is massive overkill no matter which vendor you get it from. In order to answer your question you must know the domain of the business well enough to know where the complexity of business rules fits in. If your business rules consist of: must have a value, must have a date greater than today, cannot be less than.....these ...


1

The hint of the error message is right on target: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause. Bold emphasis mine. Per documentation: RETURNING queries on the view will be rejected if there is no RETURNING clause in any available rule. Don't confuse this with the RETURNS clause of your function. Not the same ...


0

I have an idea about mongoDB. You can pre-create the quiz by using as field names the correct answer (true or false). When the player gives an answer you will have to check if the field exists. An example document is the below one: {Question:"":{true:{Question:{"text goes here",false:{}...}}} So if he answer true the first in order to validate you have to ...


1

Normalization says nothing about the Decorator Pattern, as normalization is much older. Check out Table Inheritance though, which I think is more along the lines of what you're talking about. http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html http://martinfowler.com/eaaCatalog/classTableInheritance.html


-2

Why go through all of this when there is an excellent FREE hosted start of the art on-line LMS available? kornukopia Check it out!


1

In relational database, you should create a table called Questions which should contain following fields: ID QuestionString QuestionType (Master, Individual, sub-question) TrueCase (here you should store the ID of the next question. Since we'll store all questions in this table, therefore each questions shall have unique ID) FalseCase (ID of the question ...


2

The issue is with creating two CONSTRAINT in Comment table with ON DELETE CASCADE one for UserId and other for TopicId, which can be fixed by INSTEAD OF DELETE Trigger. Just create all the tables and add all foreign key constraint as shown. You can add your columns as indicated with other columns and Id column can be changed from bigInt to Int. CREATE ...


0

Not strictly an answer... but... my 2cents (not interest bearing, might interest ing). So, Security X has identifier (ISIN or CUSIP). This identifier doesn't identify the trade, exchange, currency - just the security. This Security has value, established by trades - which fluctuates over time, place... This is tracked by deals. A deal requires a Security. ...


1

I think the easiest thing to do is to create a process that creates a log of how much stock was bought in a day and how much was left at the end of the day. You can then run your analysis on this log. The analysis could look at the last week or however long you want to determine what the average number of items sold were. You could then compare that to ...


2

The way that datafiles work, any new data will, mostly, get put in the new data file you added. Or in your case the two new data files. However, SQL Server will not try to re balance your data. This would't make sense either as you will be causing a lot of IO / load on a database for, as far as SQL server knows, no reason. You can however rebalance your ...


0

I'd look at SQLite if I were you but if you are you going to perform many analyses on this data - i.e. windowing functions -or CTEs (common table expressions), in that case, I'd go with PostgreSQL.


1

Just for a start: Usually, this should become clear from normalizing the data. You need a separate table for each entity. And you require an intermediate table for many to many (m:n) relations. With hotel and room/reservation, it's unlikely that a specific room/reservation relates to more than a single hotel. For meals, you need to decide, what exactly ...


2

I think that you really need an ER diagram here. Try YED for doing the diagram. It helps you visualise things better. I'm involved in designing rdbms for critical gov operations and was taught in university to never design things in code. I feel some of your tables have too many fks and are breaking the normalisation process. If you have an ER diagram i ...


0

I was taught by a really good teacher in relation to this. One table per entity for 1NF. You may need to create more tables in 2NF and 3NF but its the best way to start it out. Always... A well designed database will mean less maintenance and more accuracy in the long run. The best way to do it at first is to write it down on paper. Define your ...


2

The number of entities is driven by the functional specs on the database. You discover entities by analyzing the subject matter. This is different from inventing entities because it feels good. You determine whether any given subject matter entity is relevant to the database by looking at the scope of the database, per the requirements. The number of ...


0

I was able to split the table up as described above. Since I did not know the data generation time a priori, the approach above did give a 6 to 10 times speed up on queries.


1

Have a single User table. Then if anything changes, such as a phone number, it changes for the lender and the borrower simultaneously without awkward reconciliation between the two tables. Your Loan table will look something like this Loan ---- LenderID -- A foreign key pointing to User BorrowerID -- Also a foreign key pointing to User ItemID ...


1

I would take the view that you model the structure correctly first and then look at things that will tweak the structure for physical reasons if it becomes necessary. I say this because many systems start of with the tweaks 'pre-built in', i.e. " I know this will be a problem so I'll construct the data like this from the outset". Unfortunately this can lead ...


2

Both solutions are viable and each has different benefits / drawbacks - the best solution depends on your data and how many of your queries need this additional column. Adding the special_class_id to the students table makes this table "wider", meaning that the performance of all queries against this table will be slightly impacted (even if they don't care ...


2

I have read your question, but not your proposed solutions, so I can't be influeced. So, my ideas is that you can do a table STUDENTS and another table, let's say STUDENT_TYPE. In STUDENTS, you put a column, ID_STUDENT_TYPE. In this column, you can set, with the ID of STUDENT_TYPE, the type of the single student. In this way, if you have to insert a new ...


0

What you are describing is an example of the general situation entity sub-typing. There are two basic ways to handle entity sub-typing: One method places common attributes in a table and distinct attributes in separate tables, linked using 1:1 relationships to the common table. Some other considerations: The common table may have a partitioning ...


0

I would have one USERS table to hold all users with a foreign key (RoleId) to a ROLES table. A non-clustered index on `RoleId will help with the performance of looking up admin users. For things like grades and location, where it's optional for some roles and mandatory for others, you have two options depending on the business rules: Make it nullable as ...


1

In general I see nothing wrong with a many-to-many-to-many table where you have all three IDs. In this case however, you have additional columns on the SkillLevel table meaning that the Skill+Level pair has it's own unique properties. Given that I would go ahead and create an additional SkillLevelID column and use it in your SkillLevelStaff table. ...


0

Having a VARCHAR as a part of an index would be cumbersome to manage although doable. I would design the tables as follows CREATE TABLE folderkeys ( id INT NOT NULL AUTO_INCREMENT, userid INT NOT NULL, foldername VARCHAR(260), PRIMARY KEY (id), UNIQUE KEY (userid,foldername) ) ENGINE=MyISAM; CREATE TABLE datatable ( id INT NOT NULL ...


1

About _id generation, as long as you don't specify _id explicitly, Mongo would handle the id for you. It generates different nonce for different collection. So you should not need to worry about duplicated _id across different collection. Reference here. I agree with other answer, use same collection and put an extra field to differentiate regular user and ...


0

Since you have determined that the two database approach is not right for you, you want to merge the two databases. (And I agree for all the little value that is) (1) One suggestion, the simplest, is to rename one of the tables and make the needed changes to the code to use the proper table instead of the proper database and table. (2) Another suggestion ...


1

You should not have two collections, that will give you headache. Have a user collection which has admin and regular users. You can then query users as a whole or look for a given type only. So if you are looking for user id x you don't have two search two collections. As for the question on duplicate ids with 2 collections, it simply depends how the ids ...


1

The design doesn't seem awful, but I think you could solve your update issue by changing how you are assigning a priority number. Instead of using 1, 2, 3, 4, ..., you could use a higher range between the values, such as 10, 20, 30, 40, .... You might find you need an even larger set like 50, 100, 150, 200, .... By increasing the range, you have some ...


2

There are basically two ways to do this in a maintainable way: Use a metadata framework to generate the data warehouse Use Extended properties (http://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx) Ad 1) The reason you are having this issue on the first place is that you are not creating metadata first and then using that metadata (with a ...



Top 50 recent answers are included