1

I have two tables in my db:

Marketers: 

Id, Username, Password, Email

Stores: 

Marketer Id, 1 Store, 2 Store, 3 Store, 4 Store, ...., 10 store

there is the names of 10 stores for every marketer in Stores table. So there is a one to one relationship between these two tables. right? I'm wondering if it would be better to just combine these two tables or not. I wan't to send a lot of query for the second table (Stores tables). so I though this would be better if I separate these two cause I rarely need the information stored in 'Marketers table'.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
cool
  • 710
  • 1
  • 6
  • 18
  • 1
    Isnt clear to me if Stores is a row with 10 store fields? Or each is a separated row? – Juan Carlos Oropeza Jul 12 '16 at 17:48
  • In every row there is a Marketer Id which is a foreign key. and the name of ten stores in ten different column. – cool Jul 12 '16 at 17:51
  • 4
    Seems abnormal. I would think you would have a Marketer table. A store Table and a StoreMarketer table which has a storeNo lookup with values of 1-10 with a unique index Marketers and StoreNo Lookup. The uniqe index and lookup ensures no marker can have more than 10 stores. You could even add a unique index to store ensuring that a single store only has 1 marketer. Your current approach allows for duplicate stores for different markters and the same store repeated. also doesn't allow for growth if you add more stores to each marketer. – xQbert Jul 12 '16 at 17:53
  • 2
    Possible duplicate of [Is there ever a time where using a database 1:1 relationship makes sense?](http://stackoverflow.com/questions/517417/is-there-ever-a-time-where-using-a-database-11-relationship-makes-sense) – Juan Carlos Oropeza Jul 12 '16 at 17:57
  • Yes, see normalisation. Enumerated columns (above '2', say) are never a good idea – Strawberry Jul 12 '16 at 18:01
  • @xQbert, could you look at this [schema](http://picpaste.com/pics/Untitled-1_copy-pASK7DrE.1468399161.jpg)? Is this the way you are suggesting? – cool Jul 13 '16 at 08:43
  • Close. I would also have a StoreNumber Table with records having IDS only of 1 to 10. I would join this to storesMarketers and put a unique index on MarketerID and StoreNumberID. This way each marketer is limited to just the 10 stores. Otherwise a storeMarketer could have any number of stores (if you want to allow this then you don't need the table) Alternatively you could control this in code instead of at the database level, but this approach ensures integrity of 10 at the database layer; and ensures a marketerID doesn't. I would also have a uniqe index on MarketerID, StoreID. – xQbert Jul 13 '16 at 13:13

1 Answers1

3

From a good design perspective, you should keep these tables as separate. for your current requirements,

  1. if you do not need data from Marketers so often, why do you need to include that in Stores. you would just end up fetching extra data each time.

  2. say if tomorrow if some new info and the mapping changes to one to many or vice versa, your current design will work perfectly fine.

  3. and of course from future maintainence view, it is easier to update current design.

although, i would also, suggest you to add an independent primary to Stores table also.

Danger009
  • 113
  • 1
  • 8
  • Why should I add an independent primary to Stores table? I don't understand. I each row I have 10 name of 10 different store. and every row belongs to one marketer, so that "MarketerID" column makes every row a unique row. – cool Jul 13 '16 at 08:49
  • How about this [schema](http://picpaste.com/pics/second-fVyRPhSt.1468404140.jpg). – cool Jul 13 '16 at 10:03
  • Please watch the schema and say what you think? – cool Jul 14 '16 at 21:06
  • this sounds perfect. i am assuming it is store ids in MarketersStores table. this structure will keep you good for any future upgrades and NO-SQL database as well – Danger009 Jul 24 '16 at 00:16