I am creating an eCommerce site that needs to have a faceted search tool so that customers can narrow down a product search via categories and classifications in the same style as ebuyer.com and Newegg.com (See left hand menus). I initially dived straight into designing a database that ended up similar to an EAV structure (I didn't know...
Have I understood db design correctly? A user can have one or multiple professions Each profession has multiple qualifications associated with it A user can have none or all of these qualifications. Are there any improvements that should be made? simplicity should be maintained. I intend to shorten table and column names. UPDATE:...
Our company has 4 business entities, so I have created 4 different databases for each company to be used for Human Resource service. Let's call that as a Group, Company1, Company2, and Company3. Even though they are all different databases, tables and store procedures(SP) are almost same except Group. Group is a database that gets summa...
Hi. I have a table, something like FieldsOnForms( FieldID int (FK_Fields) FormID int (FK_Forms) isDeleted bit ) The pair (FieldID,FormID) should be unique, BUT only if the row is not deleted (isDeleted=0). Is it possible to define such a constraint in SQLServer 2008? (without using triggers) P.S. Setting (FieldID, FormID, isDel...
This question has come up a few times in various forums in my searching, but none have provided a concise resolution. If I have the following tables: User +- id +- username +- password Article +- id +- title +- content and I want to join them in order to determine who created what articles, I could simply add the column user_id to A...
Hello guys... I´m designing an ecommerce database... I´d like suggestion to design the following case : A product may have several sizes and several colors Each combination of products´s size/color must have a specific price So, I can have a Product X with sizes: A,B,C and colors: Green,Black and White And each combination have its ...
Let's say I've got a web application that is a store of some kind. I have a table that holds all of the monetary transactions. custid, orderid,amount paid...etc And this table is being used for sales reports and what have you. Now we want to give a customer a credit of some kind, either a gift certificate or a refund. is it a bad i...
I've been assigned to build a questions & answers widget for my company. Many different departments in the company will want to use this widget on various websites and they will want to filter questions based on specific and unique criteria. The battle here is scalability vs efficiency. Should I: A) Make unique mapping tables in the ...
I'm trying to find a good standard for a database schema that will let me do a couple things. Mainly, I am writing a web app that needs to handle various types of logins. First being the standard ASP Application Services login, Second being OpenId/oAuth logins and third being Active Directory logins. What's a good suggestion for a dat...
I'm struggling to find an efficient and flexible representation for my data. We have a many-to-many relationship between two entities which have arbitrary lifetimes. Let's call these Voter and Candidate. Each relationship has a measurement which we'd like to summarize in various ways. These are timestamped and are guaranteed to be within...
I want the database diagram to be designed for the following attributes applying normalization to it. So if anyone knew it please get back to me with answer. This is the HR Database ID. Field 1 Employee ID 2 First Name 3 Middle Name 4 Last Name 5 Initial 6 Father's Name 7 Mother's Name 8 Gender(...
I'm creating a web application wherein users should be able to specify their weekly schedule. Here's my DB model for that: id user_id day from to day is an enum of 0-6 (0 for Sunday, 1 for Monday, and so on). from and to represent minutes from midnight. So, 0 means midnight, 1 means 12:01am, 2 means 12:02am, etc. Is this a good desig...
Many people say that I should create a friendships table(userid , friendid). My question is: Won't there be a double record for every friendship ? like: 12 is friend with 5 + 5 is friend with 12 Is there a way to prevent this ? | OR | Should I just ignore that ? ...
If I have a parent and a child table filled with data, is it trivial to add a new table between them? For example, before introduction the relationship is: Parent -> Child Then: Parent -> New Table -> Child In this case I'm referring to SQLite3 so a Child in this schema has a Foreign Key which matches the Primary Key of the Parent T...
I have several Products - Plants relationships that I need to create tables in SQL SERVER. A product (around 8000) is manufactured in all 8 Plants but rarely (3-4 products out of 8000) a Product is manufactured in one/two Plants. I am thinking to implement relation-ship in either of two ways. (I know 1st approach is better but 2nd appro...
I'm trying to make a simple "fill in the blanks" type of exam in django and would like to know what is the best way to design the database. Example: "9 is the sum of 4 and 5, or 3 and 6." During the exam, the above sentence would appear as "__ is the sum of __ and _, or _ and __." Obviously there are unlimited number of answers to t...
For my web app i need to handle access requests. Requests are entered for a monday-sunday schedule specifying hours for each day. This week schedule can repeat up to 52 times. A user can have multiple schedules during a week (mon 8-9 and mon 10-11) There are the following requirements: Searchable/filterable Detect overlapping requests ...
Hey today I was in a job interview where they gave us a tree with unknown structure(could b not binary) and i was asked to insert it to a database in a certain order where I could turn back the db into tree and show I have done it using ER model, every node in the tree has two data members data1 and data2 can someone answer or give me...
Hi, anywhere I read that values in select boxes (or anything else in the html code) should not be the primary key of the database table. For example: <select> <option value="1">Value 1</option> <option value="2">Value 2</option> </select> In the database there are lookup tables with these values as primary key (1, 2, 3,....
I was wondering if you have a website with a dozen different types of listings (Shops, Restaurants, Clubs, Hotels, Events) that require different fields, is there a benefit of creating a table with collumns defined like so Example Shop: shop_id | name | X | Y | city | district | area | metro | station | address | phone | email | website...