Tag Info

New answers tagged

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 ...


1

Thanks for such thorough questions, and sorry for all of confusion - commenting 1 year after initial answer is hard and now 3 years after.. initial thoughts fade and confuse me too, but I fear to edit the hole thing because I'm not working on storing notifications on backend right now and not sure if I make good judgements without practical application I ...


0

It looks like you're wanting to store a tree structure. This is a bit awkward with databases, but it's definitely possible. Here's a link which explains how: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ The "Nested Set Model" part is what you're looking for. Applying that model to your scenario, you would get a table that looks ...


0

When you make a purchase, you're really only inserting into two tables: Purchase and ObjectsList. Querying for a Purchase and the Products in a Purchase is simple: SELECT * FROM Purchase JOIN ObjectsList ON ( Purchase.idPurchase = ObjectsList.idPurchase ) JOIN Product ON ( ObjectsList.idProduct = Product.idProduct ) WHERE ...


1

Pros for Single table: Fewer tables therefore less maintenance. If a record could change from inbound to outbound you just need to change the indicator. Cons for single table: There's a bit more effort in code logic. You can't use foreign key constraints on one type versus another. Pros for separate tables: Logically easier to comprehend and code. ...


0

Here's a model that I think would work for you problem. For assignments that take place for all week days, you can create one row in AssignmentDay per WeekDay for that Assignment. I'll create an example scenario and sample data to explain: Jim (idUser=1) is working on the American Express project (idProject=1) every day between Jan 1st and Feb 15th, ...


0

I actually think your diagram is good for your rule. The only thing is that with the relationship line connecting "Cash Accountability" and "Cashier", I would remove the circle on the Cashier side, to indicate that a "Cash Accountability" has to have exactly one Cashier. So as it is now, the relationship states that a cashier cashes out many times in a ...


0

I think there would need to be a MemberPhone table, since the description mentions storing multiple phones per member I would create tables to store the possible values for for BicycleMake, Bicycle (which contains an attribute linking to a model), BicycleColor, and BicycleSize. Given these tables, there wouldn't need to be a reference to the BicycleMake in ...


1

Here are two different possible solutions. Solution 1: Solution 2: For both models, Users is a parent table, with different tables subclassing it. This is achieved by the primary key of the child tables also being a foreign key to the Users' primary key. Solution 1 involves having a list of possible user roles in the Roles table (Administrator, School, ...


2

You could expand your activities table so that instead of a single timestamp column, it includes two timestamps, ts_from and ts_to, that indicate the time period for which the row's data is applicable. As long as no data changes, you do not insert any new rows into your table. When data does change, you insert the new data into a new row and update the old ...


0

Another alternative is to combine the teachers and students in a single table. Add a flag or attribute to identify who is a teacher or a student. This has the advantage of allowing a single user to change from one to the other or even be both at the same time.


0

I think the answer depends upon what you are trying to achieve and how the data will be used. Think more about the application design requirements. From what you describe, I would recommend a single table with 1 key, datetime & value per row. ( I think this is what you describe in your 2nd option) The reason I say this is this is that its easier ...


1

I'll try to explain why they merge all relationships into one table. Whether a users follows, likes or comments a photo all these activities are somewhat similar they involve a user and photo. Note that the comment also requires some text. So instead of having 3 tables like your data model they went with one. I don't like that approach though because it ...


0

Without any knowledge of CouchDB. Storing every version though may only differs marginal from its predecessor is a waste of storage. I'd recommend storing changes only. You might want to take a look here or search for data versioning.


0

The tables student and major are good. However following improvements can/should be made. Merge the FKs in MajorStudent to a PK, getting rid of the id Save a StudentFK and MajorFK rather than a MajorStudentFK in Exam, well one can discuss about this though but it'll be faster and in my consideration NOT redundant Grade is not normalized. All attributes ...


1

Approach 1 is the cleanest with no redundancy. However I can understand your worries about scanning through a huge table just to count a post votes. So approach 2 which features some "caching" seams fair. The downside is that you add some redundancy but this is not even the main issue. You also lose consistency. It's possible to have a post with 500 votes ...


1

I'd go with the 2nd approach. Don't be afraid of Join(t)s. Here are some reasons Fast enough. Chances are extremly low that you'd rely on the additional speed you gain from a flat structure, if there is any. It's not worth such an ugly hacky design More flexible. You can add a 5th timeframe per value later and don't end up with empty columns Less ...


0

Neither of your approaches is good. You have Tests that produce Results for some Measurement that is done with some Method. The Method may applies to multiple Test. (see sample Data below) So this is my approach Test(**TestID**, Name, Description, ...) Measurement(**MeasurementID**, TestID[FK], Description) Result(**Measurement[FK]**, **ResultNr**, ...


0

Yes this design is poor, just imagine to add another hour. This is a better aproach: Students(**StudentID**, LastName, ...[other Student Details]) Courses(**CouseID**, Description, ...[any other Course Specific Stuff]) or to whatever they attend. If you can be sure that there is and will always be only ONE you can skip this If possible have something ...


-1

AuthID does not belong in the Book table at all because a book can have more than one author. My suggestion: Author (AuthID*, AuthName) Book (BookID*, BookTitle) BookAuthor (BookID*,AuthID*) *=key attributes(s). No nulls required.


2

Assuming we are talking about 1:1 relationships among all tables. Overall storage is practically always (substantially) cheaper with a single table instead of multiple tables in 1:1 relationship. Each row has 28 bytes of overhead, plus typically a few more bytes for extra padding. And you need to store the PK column with every table. And have a separate ...


0

A select on a single table should always be faster. As soon as you have found your vehicle you already have all the details. However you lose the efficiency of normalization. For example if 1 car had many models with different options. Is this a reference db of all cars? Or a list of second hand vehicles? Would there be many examples of the same ...


1

I could e.g. add a column to the m-to-n mapping table. Is this a valid solution? Yes, this solution desrcibes best your scenario. A table with (StackBox, Item, Position) and a unique constraint on (StackBox, Position) (meaning only one item in each place/position) and probably another unique constraint on (StackBox, Item) (unless you can have an item ...


3

The only sensible design I can think of - since books can have 20 authors - is to always use a junction table and don't bother storing the AuthorID in the book table some of the time (and don't even think about storing a comma-separated list of AuthorIDs, please, or adding Author2, Author3, ... columns). This will just make queries complex. ...


0

Google "open source project management software" - I got 49 million hits. There appear to be interesting options here, here and here. There is very little that is truly original in software. What I would advise you to do is to download (a few of the) projects which look interesting, then check out their data models - that's the beauty of Open Source - look ...


0

A Google of "open source subscription software rent a bike" gives a number of interesting results, including this one. You can take and adapt ideas from some of these programmes - the beauty of Open Source. [EDIT in response to @evilcube]. Well, the pointer to Open Source software was so that the poster could download the software and then formulate their ...


0

It would depend on the size of the data you want to save in that field. You should know that there is a limit for VARCHAR, and that limit is in bytes, so number of characters that can be stored will depend on the character set you use. For example, if you use UTF 8, you will be able to store less amount of data. To sum it up, there is no "best" solution, ...


1

True story: I was a student at my school, then I taught there. Then one of the teachers took my class. You need to account for silliness like this. And a faculty member or student isn't necessarily a user either. Use table inheritance. Faculty : Individual : Party Student : Individual : Party Where : means inherits. Party hasOne User A contact is a ...


0

An identity field is meant to be a surrogate key field. The fact that it is the PK assures uniqueness, not necessarily the identity itself. No meaning should ever be assigned the value of a surrogate key. That means you can assume nothing about the associated row, not even the sequence when it was inserted into the table. That is, you should not assume id ...


0

Actually, keeping current and historical data in the same tables saves a lot more than a few where statements can overcome. Keeping data segmented (by year or otherwise) means you have to examine each query and change the target of the query itself. This usually involves dynamic SQL which is (and should be) always the last solution to consider. I've given ...


1

This is a spin-off from your option two. It uses two design patterns, namely class table inheritance and shared primary key. In addition to having two tables, one for companies and one for persons, have a third table, called contacts, that is a generalization of companies and persons, regardless of type. Contacts.ID will be the usual kind of primary key, ...


3

Though I have serious doubts in your description, let's first clean up some misconceptions. Oracle is what is called a Relational Database Management System or RDBMS (the company developing it is called Oracle For short, too). It is used – who would have guessed it – to store and retrieve data based on its relations. It requires an operating system to run. ...


0

Centralized approach Requirements for each user view are merged into a single list of requirements for the new database system. A logical data model representing all user views is created during the database design stage. The centralized approach involves collating the requirements for different user views into a single list of requirements. A data model ...


0

If it bothers you to include WHERE AcademicYear = '2015/16' very often, you could instead create a view which includes this filter: CREATE VIEW CurrentEnrollment AS SELECT * FROM Enrollment WHERE AcademicYear = '2015/16' My understanding is that MySQL does not support materialized views or filtered indices, so you're not going to get any ...


1

How large do you expect the data to grow? No harm in keeping older data in the same table. You could plan to use partitioning and implement date range partitions (per year). Using WHERE statements is completely natural in SQL so don't consider this as "messy" at all.


2

Rather than calculate the value in code, consider a computed column instead. ALTER TABLE dbo.Customers DROP COLUMN Id; ALTER TABLE dbo.Customers ADD Id AS (CASE CustomerType WHEN 1 THEN N'P' ELSE N'x' END + CAST((SequenceId + 1000000) AS nvarchar(10))); Below is an example of the insert proc using this computed column: CREATE PROCEDURE ...


3

You never set the SequenceId to anything. It is not possible to set the value of a column during an insert using an expression that references the value of an IDENTITY column on that same table that will be set upon that same insert operation. If you think about it this makes sense because the row doesn't exist until you insert it, so there is no IDENTITY ...


1

SequenceID is invalid,since you are not specifying from where to get the value.I suggest,create one more temp variable and add ,some thing like below declare @seqid int select @seqid =next value for from your sequence then in place of sequence ,keep this variable


0

One method is to have two sets of tables (This1, This2, That1, That2, etc.), or a set of tables in separate databases/schemas. Either way, you now have SetA and SetB. Have a set of views that access one set of tables, say SetA. Perform the nightly imports to SetB. When the new data is ready to go, recreate the views, pointing them now to the SetB tables. The ...


0

One obvious disadvantage is that no matter how good your data model is, it better suit your needs for years to come because you will have just set it in concrete. I try not to even give app developers access to the the underlying data model. There is a "wall of abstraction" between users and the physical data. That wall is build from a wide variety of ...


0

As long as you have a date field that marks the time the score changed, you can perform a versioned query to return the score that was in effect at any given time. Here is a recent answer that describes the query.


0

Sounds like a single table in a single database. It sounds like you need three columns: account, date, score. The PRIMARY KEY might be (account, date). 10K rows is "small" for a table.


0

You can achive this by creating History table. In that table you can store all changes from those tables. You have to use tiggers to populate data in History table. Take into account that if the history table stores all changes you need engine to populate it. Another approach is to have History table for each table that you want to observe. In your case ...


0

The meta table gives you flexibility of design (new "fields" are added and removed using DML instead of DDL), but its inefficiencies means it is not very scalable and it can be an absolute bear to maintain. Depending on how many users will be tracked, it could work well for you. The only way to know for sure is to build and test it.


3

A simple solution that has worked well for me in the past is to store all your data in the 'base' units. For example, your base unit for lengths may be millimetres and your base unit for weights may be kilograms. This solution may result in the need to convert some of your existing data into the base unit, if it isn't already. Once you have all the data in ...


0

I'm assuming this is K-6 not college. Otherwise the "one class per student" rule doesn't make sense. A Class entity has no attributes? Like Grade( K-6), Teacher, Level (regular, learning disabled), room number, etc. I think what you are really after is an intersection between Class and Students. A class is made up of many students. A student is registered ...


0

I prefer this format, using "crows feet" to illustrate the many-to-one joins


0

I take it that your main concern is tracking the changing rates and charging the prevailing rate when work was performed. That's not really a problem. There is a pattern I call Temporal Normal Form. It's a way to track changing data using normalization -- a pattern we all know and love. So the Rates table will be normalized according to static data: ...


1

here's my observations. :) 1/ Your posts should only refer to a car_model. There is almost no performance issues to do an additional join given the fact that there won't be a load of car makers. More about that, you are running into an integrity problem : what if you have a problem with an update and found that your post is referring to an 'Audi A3' but is ...



Top 50 recent answers are included