Tag Info

Hot answers tagged

16

I don't think you have a problem with the relationships. I think instead the problem is that by using surrogate keys (ie Ids) for each table the resulting database is unable to prevent Workers from being inserted whose Department is of one Company while the Classification is of another and vice versa. A good way to understand this is to visualize the ...


6

For your first database class and first attempt at an ER Diagram (ERD) I think you have done a great job! I'd like to give you some feedback in the context of the process I use to break down a set of requirements like you were given and create a draft diagram. Hopefully by taking this approach I'll help you in your development of the skill of ER modeling ...


4

Your issue stems from the fact that there is an entity type missing from your model. Consider the following ERD: Note that I've added an intersection entity type between DEPARTMENT and CLASSIFICATION. This new entity type: POSITION provides the information which is implicit in your model, that a particular department has a give set of jobs of various ...


4

Model Here's a possible model. After I drew it I noticed that the difference with yours is that the product part number is now in the Product table; I think it should be this way as it's a characteristic of the product. Then the Job refers to a Product via its part number, and not viceversa. Each table uses a surrogate primary key for clarity. This is not ...


4

This isn't really a database administration question, but... Firstly, your pattern does not prevent entries like this: XXXINT123abogus, because it does not say that "INT" or "NINT" should be at the beginning of the string, neither does it say that the lowercase letter, if it's there, should be the last symbol of the string. You may want to try something ...


3

Which of the two options is the best? The relational design is clearly superior to the hierarchical design used in the NoSQL example. A database schema designed using relational principles does not favor one access path over another. Each table represents a real world entity type and through the use of relational algebra queries of arbitrary ...


2

If the only thing that inserts / updates data in this table is an out-of-hours process I'd be tempted to apply as many indexes as you can. 999 are allowed for a SQL Server 2012 table. You can determine how many you need using the Database Tuning Advisor. Obviously this will slow down the load process and consume a lot more disk space - both from the ...


2

I think you have a good start on this. The main thing that jumps out at me to improve the schema is the need to distinguish between things and types of things. In the requirements there is really both a product type - say a model 250 computer - and products - assembled instances of model 250 computers. The product type is known by a model number, whereas ...


2

A foreign key can't be made conditional so that is out of the question. The business rule appears to be that an employee can work for one and only one physical store. Given that, the super type of store has two sub-types as you suggested: Physical and Online. Each physical store may be staffed by one or more employees, and each employee must be assigned ...


1

You are talking about less than 5 queries per second. A well maintained database can handle 100 qps or more. A poorly handled db will melt down even with 5 qps. It is rarely a good idea to split a table artificially. It is usually a good idea to normalize repeated data. But don't normalize 'continuous' data, such as numbers, dates, floats, etc. What ...


1

Presumably the Disp_id is a non-unique field as you'll have an order table which contains the customer name / delivery address etc so you'll have an order has 1-+ dispatches The product table will cant be a 1-1 relationship with dispatch otherwise only one person would ever be able to order that product regardless of how many they order so the product -> ...


1

I would state it like this: Each Person may be the holder of one or more Accounts. Each Account must be held by one and only one Person. Given that, let's address a few of the questions and statements posed: Person do not have necessarily one Account (can be null) The Account would not be null. Instead, there just wouldn't be a row in Account for ...


1

An Account belongsTo a Person. A person can exist without an account. If a Person hasOne Account, then Account's primary key should also be a foreign key to People. create table people ( person_id int primary key, meta1 text, meta2 text ); create table accounts ( person_id int primary key references people(person_id) on delete cascade, meta1 ...


1

Of course, the indexes need to be prioritized. You can create only those indexes that would impact most number of users, or most critical users, or would have maximum impact on the system. Look at the following blog for an easy way to identify missing indexes: http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx


1

In the spirit of learning, since this is homework, I'm going to give you some feedback without giving you the diagrams. I'm also going to use some SqlServer specific terms but you should be able to figure out what I'm saying with minimal research. One thing that immediately jumped out at me is your use of the SSN as a PK. I know this seems like a great ...


1

Maybe, you should use staff, attendance_staff, and school. staff table ========== id, fullname, birthday ... school Table ============== id, name, founded, number_of_staff ... attendance_staff table ======================= id, staff_id, school_id, attended_date, ...


1

An activity feed simple generates a log of activities with the most recent first. There are a few ways one can implement an activity feed for a user. E.g. John Smith's answer is one approach. Everything depends on support you want to cover.


1

By having three tables in the database (e.g. activities, user, and user_activities) and a simple LEFT JOIN SQL query as the one below can be executed: SELECT SELECT user.name AS user_name , activities.name AS activity_name FROM user_activities LEFT JOIN user ON user.userid = user_activities.user_id LEFT JOIN activities ON activities.activityid = ...


1

This doesn't call for any SCD design -- the state of an entity is not changing, slowly or otherwise. What you have is a series of events that you want to refer to the user associated with the event. create table Executions( ID int not null auto_increment, UserID int not null, ExcDate date not null, <any other data concerning the ...



Only top voted, non community-wiki answers of a minimum length are eligible