Few questions, though i'll ask it all in 1 post. 1) Any difference between a Lookup & reference table? And what is best practice on how many to use? Example List of Cities, Countries, Account types, Category names, User tags, Movie names - are these all lookup, reference or some other type of tables? Key is all this data will be search ...
I mean analyzing 2 users profiles I get a score that it is reciprocal a.affinity(b) == b.affinity(a) I'd like to know in particular: which schema would u use to implement the affinity table which db mysql, redis,.. which technology would you use to update the affinity score in background? Thanks ...
Hi. Consider this very small contrived subset of my schema: SensorType1 ID : PK SensorType2 ID : PK Reading Timestamp Value SensorType1_ID FK -> SensorType1 SensorType2_ID FK -> SensorType2 Some readings are for SensorType1, some are for SensorType2. I would probably add a constraint to ensure exclusively one of those FK's is alway...
I am creating a social networking site and am working on a friends table and dont know what Im doing wrong. I have a users(userId, name - userId is primary key) and another table called friends(friend1, friend2, status). friend1 and friend2 are foreign keys to userId on update and delete are set to cascade table users has an entry wit...
i have an event calendar application with a sql database behind it and right now i have 3 tables to represent the events: Table 1: Holiday Columns: ID, Date, Name, Location, CalendarID Table 2: Vacation Columns: Id, Date, Name, PersonId, WorkflowStatus Table 3: Event Columns: Id, Date, Name, CalendarID So i have "generic events" whic...
I have a MS SQL table with about 8 million records. There is a primary key (with clustered index with only 0.8% fragmentation) on the column "ID". When I run seemingly any query referencing the ID column, the query takes very long (and in fact ultimately crashes my application). This includes simple queries like "SELECT * FROM table WH...
I want to allow my users to opt-out of certain email notifications. I'm trying to decide how to implement this in the database. I've already got a set of Users and all the emails are stored as EmailTemplates which have a unique key. So I figure a simple m2m table with users/emails would work well. However, should I (1) populate this ta...
I am having confusion in designing the database. Its a Subscription based application : One Subscription can have multiple display devices. While Subscribing, the user will be asked to select one of the displayed devices. Keeping this in mind, I've come up like this: Is the above approach correct? Should the USER_SUBNS.DISP_DEV_...
Hello, I need to store an retrieve a vector of an unknown number of objects in an android sqlite database. Essentially, the setup is this: I am developing a task management app, where the user can add as many notes as they like to their tasks. My current setup uses one database, with one row per task. This presents a problem when I need...
I have a db with many tables. Some tables have unit price column, no tax, gst and such columns. What should i do now? Should i create GST table, HST table and PST table separately. In other words, what is the standard schema of designing the tax table? ...
I have an employees table, and I want to track their Operating System preferences. They can have more than one OS preference: Employee - ID - Name - OS_Prefs_ID OSTypes - ID - OperatingSystemName - Version OS_Prefs - ID - OSTypes_ID Is the relationship between Employee and OS_Prefs "one to many" or "many to many"? I am gue...
hi all i want to know that while we create a forigen key for any table then why we use constraints like.. CREATE TABLE Persons ( PersonID int identity(1,1) NOT NULL, FirstName nvarchar(20), LastName nvarchar(20) NOT NULL, CONSTRAINT PrimKeyPeople PRIMARY KEY(PersonID) ); here all ready PersonID is primary key then why ...
I've been wondering lately. Lets say we're doing a webapp with JSF+Spring+JPA/Hibernate (or well any other technologies) and lets say we have a "User" entity. We want the User to have a unique login. If we want to do it then we can put a @UniqueConstraint on the "Login" column, but still our application has to check during user registrat...
When using a stand alone database as a membership provider, is it usual to then have an application specific users table in the database serving the application? For example say I have an application that manages messages for a user. Normally I would have a user_messages table and reference the users table foreign key? However if I just ...
1. So if I search for the word ball inside the toys table where I have 5.000.000 entries does it search for all the 5 millions? I think the answer is yes because how should it know else, but let me know please. 2. If yes: If I need more informations from that table isn't more logic to query just once and work with the results? An exa...
I am about to embark on a project which will be a database with potentially thousands of entries. It is for an Australian audience so load should not be particularly high. The developer who is interested has a strong preference for RoR. However I am unconvinced of the benefits of RoR for all aspects of a project. For example, while RoR...
Choosing good primary keys, candidate keys and the foreign keys that use them is a vitally important database design task -- as much art as science. The design task has very specific design criteria. What are the criteria? ...
I want to represent documents in a database. There are several different types of documents. All documents have certain things in common, but not all documents are the same. For example, let's say I have a basic table for documents... TABLE docs ( ID title content ) Now let's say I have a subset of documents that can ...
I have a table called WorkItemNotes, and separate tables for Customers and Employees. I am using MySQL WorkBench to map out the database tables and relationships and have a couple of questions. Both Customers and Employees can add notes to the WorkItemNotes table, and the associated ID will be stored in the table: | WorkItemNotes | | ...
I am designing an app that tracks orders. Each order can have > 1 workItem, and each workItem can have a separate price. I was storing the prices of the workItem in the workItem table, thinking that in the UI, or for reports, gathering the job cost (both billed to the customer and paid to the contractor) would be calculated with a quer...