I am designing a web site for online shopping and I have a question related to that. As you know that shopping website may have different product with different statistic/specification. What is the best way to design a database? Below are the thoughts which comes to my mind   Creating different tables for different product type. Ex. Phone, Tv, Video Games, Watches, Clothes, Computer etc.      Concern:   but let's say a to show mobile specification [just like  example] I need to have around 40-50 columns to store a mobile specification. And let's say tomorrow one new feature comes to mobile then I probably have to add one more column which will hold the value for that specific model and for every other mobile that column will be NULL.   2: Having major product tables ex. Electronics, clothing and accessories, health etc.. and a separate specification table for each table. Ex. Electronics table will hold the master details for tablets, mobile, land line etc. and Electronics_Spec will contains the columns with related specification.     Concern:   There are many characteristic between products which does not match with others. Ex. land line phone will have a specification about wire length while as tablets won't.      General concerns:   There are certain products which has quite less specifications. Ex: bed, toys , footwear.  Is there any other better way to design a database to hold huge specifications related to multiple products?   My intention is to save database space to the best. I sincerely apologize for my bad English.
121764
I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand whether I need specify all the specifications in a table or do I need to use different tables for different products?  For example, consider the products Mobile and book, each of these having unique specifications (such as color,size, cost, model for mobile and title, ISBN, author, cost, year_of_publication, etc. for book), if it is only less number of products then it is possible for me to design, but when there is a thousands of products, it takes a lot of time.  Can anyone tell me how to design my database to manage this situation?
6875
I am trying to find some database tutorials,books and similar, since I have started this course in college, and received some material, but it is really hard to manage through all the info on the net. I searched through the questions on the site but didn't find any. Thankfull for all your input.
75785
I do not have much experience in table design. My goal is to create one or more product tables that meet the requirements below:   Support many kinds of products (TV, Phone, PC, ...). Each kind of product has a different set of parameters, like:   Phone will have Color, Size, Weight, OS... PC will have CPU, HDD, RAM...  The set of parameters must be dynamic. You can add or edit any parameter you like.   How can I meet these requirements without a separate table for each kind of product?
20339
the two bits of SQL below get the same result  SELECT c.name, o.product   FROM customer c, order o   WHERE c.id = o.cust_id   AND o.value = 150    SELECT c.name, o.product   FROM customer c   INNER JOIN order o on c.id = o.cust_id   WHERE o.value = 150   I've seen both styles used as standard at different companies. From what I've seen, the 2nd one is what most people recommend online. Is there any real reason for this other than style? Does using an Inner Join sometimes have better performance?  I've noticed Ingres and Oracle developers tend to use the first style, whereas Microsoft SQL Server users have tended to use the second, but that might just be a coincidence.  Thanks for any insight, I've wondered about this for a while.  Edit: I've changed the title from 'SQL Inner Join versus Cartesian Product' as I was using the incorrect terminlogy. Thanks for all the responses so far.
112267
I want to know if I using different databases can help performance.  I have one big table support_chat with more than 5 million rows. I need to scan this all the time (3s with ajax) to check new messages on support.  If I move this table to another database in the same computer, can it help anything? (let users, sells, comments table faster)  For example:  db1  users table sells table comments table support_chat table // move this table to db2   db2  support_chat table   They want to keep old things due legal reasons, to have a user support history.
52173
I am working on an application which records data from different forms. New forms can (and will be) constructed dynamically by an admin. I am wondering what would be the correct way to save this data in a database? My first guess would be to use EVA, but considering all the bad publicity I am a bit hesitant.  Let's say I have two forms:  #form 1 description = TextField() score = IntegerField()  #form 2 feedback = TextField() NOR_score = IntegerField()   There is no way of predicting the number of possible attributes. I am playing around with two ideas, but maybe you'll be able to suggest a much better approach.  Idea I:  unique_id | entity | attribute   | value 1         | form 1 | description | test123 2         | form 1 | score       | 90 3         | form 2 | feedback    | blabla 4         | form 2 | NOR_score   | 5   Idea II:  unique_id | entity | value 1         | form 1 | {"description": "test123", "score":"90"} 2         | form 2 | {"feedback": "blabla", "NOR_score":"5"}   Thanks in advance for any suggestions.
10493
Do you know a good source to learn how to design SQL solutions?  Beyond the basic language syntax, I'm looking for something to help me understand:   What tables to build and how to link them How to design for different scales (small client APP to a huge distributed website) How to write effective / efficient  / elegant SQL queries
116532
Lets say I have a table to hold Tasks and then I have a table to hold Sub Tasks, is it better to break Tasks and Sub Tasks into separate tables or keep them both in the same table with a possible ParentId column.  What if a Parent Task could also be a Sub Task, does this change the design?
94152
As a read in lot of links , for e-commerce shopping sites, as it may require to add lot of attributes in regular basis, is it okay if we go with Flat tables ?  Whats the disadvantage of changing database schema or inserting/deleting new columns &amp; tables in middle of the project ?   also Is there any problems if we keep lot of NULL values in database ?   As i heard EAV is Evil , I want to create shopping site with flat tables....
49743
If we have to store the available positions at a company (i.e. Manager, Team Lead, ... etc). What are the best practices for storing it? I have two opinions with comments... "sure, welcoming yours"        Storing it as DB table with columns ID and Name, and deal with it using queries and joins.     Storing it as Enum and forget about the DB table.    In my opinion, I will choose the first solution if I have changing items. So that I won't hard code these options as Enum. I may choose the Enum solution, if I have no doubt that data won't change (for example, Gender: Male, Female).  NOTE: I code in English, and the UI Culture may be Arabic. If I will work with the Enum Solution, I will hard code the culture-based strings in the presentation layer, is it okay from the best practices perspective!!!!  I would like to know your opinions and if my thoughts correspond to what is most recommended "Best Practices"??
73516
There's this side project I'm working on where I need to structure a solution to the following problem.  I have two groups of people (clients). Group A intends to buy and group B intends to sell a determined product X. The product has a series of attributes x_i, and my objective is to facilitate the transaction between A and B by matching their preferences. The main idea is to point out to each member of A a corresponding in B whose product better suits his needs, and vice versa.  Some complicating aspects of the problem:   The list of attributes is not finite. The buyer might be interested in a very particular characteristic or some kind of design, which is rare among the population and I can't predict. Can't previously list all the attributes; Attributes might be continuous, binary, or non-quantifiable (ex: price, functionality, design);   Any suggestion on how to approach this problem and solve it in an automated way?  I would also appreciate some references to other similar problems if possible.    Great suggestions! Many similarities in to the way I'm thinking of approaching the problem.  The main issue on mapping the attributes is that the level of detail to which the product should be described depends on each buyers. Let’s take an example of a car. The product “car” has lots and lots of attributes that range from its performance, mechanical structure, price etc.  Suppose I just want a cheap car, or an electric car. Ok, that's easy to map because they represent main features of this product. But let’s say, for instance, that I want a car with Dual-Clutch transmission or Xenon headlights. Well there might be many cars on the data base with this attributes but I wouldn't ask the seller to fill in this level of detail to their product prior to the information that there is someone looking them. Such a procedure would require every seller fill a complex, very detailed, form just try to sell his car on the platform. Just wouldn't work.  But still, my challenge is to try to be as detailed as necessary in the search to make a good match. So the way I'm thinking is mapping main aspects of the product, those that are probably relevant to everyone, to narrow down de group of potential sellers.  Next step would be a “refined search”. In order to avoid creating a too detailed form I could ask buyers and sellers to write a free text of their specification. And then use some word matching algorithm to find possible matches. Although I understand that this is not a proper solution to the problem because the seller cannot “guess” what the buyer needs. But might get me close.    The weighting criteria suggested is great. It allows me to quantify the level to which the seller matches the buyer’s needs. The scaling part might be a problem though, because the importance of each attribute varies from client to client. I'm thinking of using some kind of pattern recognition or just asking de buyer to input the level of importance of each attribute.
6965
I am building a database system for my retail business. I have set some tables which are:   Product Purchase Sales Balance   All are connected one another and are able to show my inventory level.  The problem I am having is I also sell bundles of products - that have different prices than their individual prices. Example: I sell an orange for $1, an apple for $1.2; I sell fruit package 1 (2 oranges and 2 apples) for $3.8, package 2 (4 oranges and 4 apples) for $7.  Is there a right way how to create relationship for these product bundles?  PS: I am using FileMaker Pro creating this.
114342
I have a sketch that I want to put up on my website, and I also intend to write a short play at some point which I'd also want to make freely available.  I'm trying to work out the best way of representing this in HTML. I basically need two columns - one for the character speaking, and one for the text. Each speech obviously needs to line up with the speaker though. In other words, something like this:       Jeff        This sure is a nice website we've got now.      Joel        It certainly is. By the way, working at FogCreek rocks.      Jeff        Of course it does. Have you played Rock Band yet? It's                  a lot of fun.   (Well it's better than lorem ipsum...)  I know how I could do this with HTML tables (with one table row per speech) but that seems pretty ugly, and everyone certainly seems to be keen on using CSS to represent non-tabular data. I can't see how this really counts a tabular data - my use of "row" and "column" earlier was to do with the layout rather than the fundamental data.  So, any ideas? I think most of the script websites I've seen (not many, admittedly) either use &lt;pre&gt; like my example above, or don't bother trying to keep the normal script format, instead just prefixing each paragraph with the speaker's name. (See the podcast wiki for an example of this style.) I'm having trouble working out even what HTML elements I should be using to represent this, frankly - a dictionary definition list with the speaker as the term and the speech as the definition is probably the closest I've thought of, but that feels like abuse.
61586
I'm noob in database. So don't mind if its a silly question.This is used in my application and is the first one. I need a database design for it.    value 4 is dependent on the other value 1,2&amp;3. even if one of the value changes the combination changes and the fourth value is affected. Each of the value 1,value 2,value 3 are independent on each other. Many-to-many relation between value 1,value 2 and value 2,value 3. value 4 is not derived or computed but a distinct value for all the combinations. So how can I design a database for it?
66948
Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to another group.  When trying to decide on a primary key for the item table, what should I use?  Should I make up an artificial global serial key like this:  CREATE TABLE items (     item serial PRIMARY KEY,     group integer NOT NULL REFERENCES groups(group), );   ... or should I rather use a composite key and per-group item serial like this:  CREATE TABLE items (     group integer NOT NULL REFERENCES groups(group),     item integer NOT NULL,      PRIMARY KEY(group, item) );   The reason why I'm leaning more towards the second solution is that the post URL will always show the group and item, so it makes sense to have both of them as the composite primary key. In case of the first solution, the URL contains superfluous information because the group ID can already be deduced from the item ID alone. The URL structure is given, however, and cannot be changed.  The disadvantage of the second solution is that you have to manage a per-group serial (i.e. each item integer should start from 0 for each group).  What's better in terms of best practices, normalization and performance? Or is it simply a matter of taste?
123699
I just had a question about database design, I am in fairly early learning stages of this so bear with me.  Suppose I have a parent table called "Projects" each "Project" Entity can have any number of "Attachments" and each "Attachment" can have any number of "Anchors".  I can only see a few possible solutions for this. Estimations assume 10 years use of this database: 300 Projects/year * 3 Attachments/Project * 20 Anchors/Attachment  Solution 1 (My initial design idea):  Involves 3 tables. "Projects"->"Attachments"->"Anchors"  Number of Tables: 3  Max entities in a table: 180,000 in "Anchors"  Solution 2:  Involves 2+n tables.  "Projects"->"Attachments"->"Anchors[n]"  Number of Tables: 9,002 (9,000 Anchor Tables)  Max entities in a table: 9,000 (In Attachment Tables)  Solution 3:  Involves 1+n1+n2 tables. "Projects"->"Attachments[n1]"->"Anchors[n2]"  Number of Tables: 12,001 (3,000 Attachment + 9,000 Anchor)  Max entities in a table: 3,000 (In Project Tables)  To summarize:  What's worse, more entities or more tables? AND/OR Is there simply a better design for this situation that I am unaware of?
4003
Our school in Delhi (India) wants to develop a professional and robust in-house database (SIS) that should handle vast amount of student information, including admissions, registration, re-enrollment, student behavior management and grading. We noticed that over-the-shelf products are unable to complete our specific tasks without major customization and costs. Hence, we decided to develop this ourselves since we have full control as to how we want our information to be reported to our education board (as per their specifications) and have control on our complex grading system (which is continuously updated to meet the dynamic benchmark needs of our education board).   We have begun using MS Access and is still in the primary stages of development. We want to know whether we should use MS Access as a front-end and SQL Server as a back-end DB since we want to accomplish the following:   Ability for 80+ employee users to simultaneously access, post, update and delete customer records within the SIS DB (on campus). Ability for our SIS DB to have web-based functionality with a student login portal integrated within our school website so 1080+ students can simultaneously access a specific web-based DB form from their homes (by using the student login portal) for updating personal records, checking grades, and other performance summaries. Ability for our SIS DB to be highly secure and functional 24/7 without interruptions and security breeches (we read that MS Access alone cannot accomplish this). Ability to integrate our SIS DB with another in-house database that will provide digital learning materials to students within the comfort of their homes (also accessed through student login portal). The digital learning materials DB is another DB we are creating that should have ability to store 100 GBs worth of multi-media materials. Ability for data to be quickly pulled from tables to the GUI without delay.   A major factor for our school in this matter is COST. They want to create the DB under USD 1,500 and would like to have many users being able to access this system at the same time. Please help me to decide how I should go about this situation, both performance-wise and cost-wise. I apologize for the long details provided. I am really confused and would deeply appreciate if any one could help me on this matter.  In addition, we wanted our forms to be professional like the ones seen in major DB systems. We noticed that Access can create forms but only to a certain extent. Is there any other system programming-friendly platforms we can use for creating highly complex and professional DB forms (lke Visual Studio, for example)? Help on this subject as well will be extremely appreciated.  Ankith