Tag Info

New answers tagged

0

I have a few observations and suggestions: Do not create a DATE table when all you need is an element (column) for the date of the POST. Generally speaking, a table with nothing but a meaningless ID and one other column is a good indication that you need to look hard at whether all you really need is the non-key column. Consider CATEGORY, this also has ...


4

Will you be ranking the billions of web sites? If so, then on-the-fly and recompute-all are out of the question. Instead, have a background task working on the problem. For display, show the info that is in the table, possibly out of date by a day or two (depending on how long it takes to recalc). Put the ranking in a separate table, with only website_id ...


1

The Products table should contain static data about the products (name, sku, weight, etc). Dynamic data such as quantity on hand and location (bin number, shelf number, whatever) should be contained in a separate Inventory table. In fact, if location is stable enough, it could instead be in the Products table. Whoever wrote the description of the dispatch ...


0

I'm assuming you'll use a appointment_date field. If so I would partition the table by [appointment_date and location_id] OR appointment_date only depending on what type of queries you'll be running most the time. Mysql supports many type of partitioning and sub-partition. 18 MR (millions rows) / years is not much (especially if columns are mostly date and ...


3

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


0

If this is a simple constraint issue on a FK then you might want to consider a CHECK CONTRAINT. Something similar to the below might work. CREATE TABLE Employee ( <Your Columns>, TransStoreId integer CHECK (TransStoreId != 2) -- Assuming 'ONLINE_TYPE' id is 2 );


0

You might be able to find somebody else's design for an order fulfillment system in one of the models presented in database answers.


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


0

Looks good if you want a Person to have 0 or many Accounts. If a Person can only have a maximum of 1 Account, then I'd put the account details in the Person table.


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


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


0

In the ideal world developers should have no rights in a production database. But we do not live there. If you want your developer to have the role as helpdesk in case of problems then you might give him select rights to check the data. If the data in the database needs to be 'adapted' then this is more the task of the DBA or somebody else.


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


0

For a Start you need Dependent Id as Primary Key in you Dependent table (Identity / Autonumber). If an employee works on multiple project you need a look up table for employee and Project, you shouldn't link Employee to project directly. Same as Project if there are multiple locations for Department, you need separate table to maintain look up between ...


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

Your question isn't very detailed, anyway here's how you could implement what you want: The simplest way to model a privacy setting schema is probably to add a visibility field to each relevant property of the user profile: User ------------------ id (int, PK) username (varchar, IDX unique) fullname (varchar) fullname_vis (int) email (varchar) email_vis ...


0

@RottenUser, you seem to be on the right-track, as far as I can confirm this, having looked at your db schema. You asked: What can be improved and how? Well, company_phones isn't really need, I think. As you can add a column in the phones table or contact_phones, sort of to filter out if the phone is private (i.e. personal mobile)/public etc.


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


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


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


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


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


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


0

It'd help more if you could be more specific about what you're modeling, as it's hard to picture at the moment, for me at least. For tree structures, in SQL, any database that supports Recursive Common Table Expressions is a good choice (any major one other than MySQL). For historical data, the History Table pattern has proven to be effective. For table ...


0

It is 'rarely' good form to have two tables with the same set of columns. It is 'usually' better to have one table with an extra column that specifies 'dataset' the row belongs to (Bravo/Delta/...). Often it is helpful to think of the schema (table definitions, etc) from an "Entity/Relation" point of view. Start with the Entities such as (I guess) ...


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


0

One option is to use Version Normal Form (vnf). The advantages include: The current data and all past data reside in the same table. The same query is used to retrieve current data or data that was current as of any particular date. Foreign key references to versioned data work the same as for unversioned data. An additional benefit in your case, as ...


0

I would have 2 columns: one for the description and one for the keyword. I would probably use MyIsam (depending on which Mysql version you're using) and use Mysql FullText search features. Innodb supports it now. I would test with both and see which ones is most performant.


0

There's no need to have one table Users and another table Persons. I've never seen a social network that allows a single user to create more than one profile. True, there are people that keep multiple profiles, but these are done by opening multiple accounts (sometimes against the TOS). You could use this model: Table users: user_id (PK) username (IDX, ...


3

There are a couple of concepts which need to be distinguished. One is about structure and the other about schema. Structured data is one where the application knows in advance the meaning of each byte it receives. A good example is measurements from a sensor. In contrast a Twitter stream is unstructured. Schema is about how much of the structure is ...


0

I'll make an assumption first. A real world person could have more than one user in your system? If a real world person (persons table) can have (and should have) only one user, why do you allow a one to many relationship between those two tables? Or I've misunderstood your model or I would create in the Logical Data Model only one table for users AND ...


5

Despite the mainstream of noSQL databases IMHO the decision about adopting such technology should be made according to the achievements needed according to the information stored, not only attending to the performance you currently have. This means that maybe your best option is to stick to the SQL database and improve your HW. But additionally I read ...


0

You will need several tables to accomplish your design. One for ads, one for categories and one for the ad properties (make, manufacturer, book title, isbn, etc...). Here is a sample design: Table Advertisement. This will contain the ad instances. ID - primary key Title Category_ID - foreign key to Category.ID. Tells us the ad category. Description ...


0

Store the information in tabular format. It will also help you in validating at application level. Store your field type in property table. and values in columns table. Hope it helps.


0

I would avoid having completely separate tables for each ad type, that would be bad design (you'd end up having to UNION them all together in just about every query). Also avoid having all the fields possible in one table as that would be inefficient also, especially if your database doesn't support sparse rows. The usual technique for this sort of problem ...


0

I would break these queries and have result stored in temporary tables. I would use a stored proc. Have sub-queries like this among many tables will create constrain and potentially some lockings. Also using > on a column in query will for Mysql to do a scan on that column (even if it is indexed). Avoid >,>= or < <= (use between).


7

As stated in comments, I would store the current or effective version of a BlogStory in its respective table and keep all of its previous versions (or past states) in a separate (but related) BlogStoryVersion table. In this manner, you may find this post helpful since it presents a comparable method for a similar scenario. Business rules In accordance ...


2

I would create a distinct emails table and use a surrogate key to instantiate the reference of the email address to the person and to the user. The model would look like this: This handles the need to associate the email address with the users as well as the persons, but eliminates the redundancy of using the actual email address text to instantiate the ...


0

CREATE TABLE Position ( Id int NOT NULL PRIMARY KEY IDENTITY(1,1), Name nvarchar(255) UNIQUE, IsDeleted bit NOT NULL DEFAULT 0 ) INSERT INTO Position (Name) VALUES ('Titi'), ('Toto'), ('Tata') UPDATE Position set IsDeleted = 1 where Name='Toto' DECLARE @Name nvarchar(255) SET @Name = 'Toto' -- INSERT Position (Name) VALUES (@Name) --RAISES AN ERROR ...


0

Why not somthing simple like this: User enters position 'President' and it does not exist in the table. Insert Into table User deletes position 'President'. Update table set isDeleted = 1 User changes mind and again enters a position 'President'. Update table set isDeleted = 0


3

Given the additional information that Aaron Bertrand didn't have access to when he posted his answer I would suggest a different tack. Instead of putting logic/business significance in table names I would have general table names and put the logic/business significance in attributes/data in the tables. This should make it easier to expand functionality, ...


2

Probably easiest would be an INSTEAD OF INSERT trigger. CREATE TRIGGER dbo.FixTable1 ON dbo.Table1 INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; DECLARE @offset INT; -- decimal? Something else? SELECT TOP (1) @offset = offset_m FROM dbo.Table2 -- WHERE ...? ORDER BY [timestamp] DESC; -- terrible column name btw INSERT ...


6

You don't need triggers or PL/pgSQL at all. You don't even need DEFERRABLE constraints. And you don't need to store any information redundantly. Include the ID of the active email in the users table, resulting in mutual references. One might think we need a DEFERRABLE constraint to solve the "chicken/egg" problem of inserting a user and his active email, ...


0

Your relation (story_id, version_id, editor_id, author_id, timestamp, title, content , coverimg) is not in 3rd normal form. For every version of your story the author_id is the same. So you need two relations to overcome this (story_id, author_id) (story_id, version_id, editor_id, timestamp, title, content , coverimg) The key of the first relation ...



Top 50 recent answers are included